Oracle having in built functionality to convert number into word format by using Juline(J) and spell out (SP) Pattern Modifiers for Date/Time Formatting.
Convert Number into Word in postgresql
for example
SELECT TO_CHAR(TO_DATE(12345,'J'),'JSP') FROM dual;
output like
TWELVE THOUSAND THREE HUNDRED FORTY-FIVE
in postgresql spell out (SP) pattern modifier not implemented (upto version postgresql 9.0)
here i have posted function to convert given number into word, lets look
create or replace function fn_spellout(p_num varchar) returns varchar as
$$
declare
v_word varchar(50);
v_spell varchar(1000) := '';
v_length integer;
v_pos integer := 1;
begin
select length(p_num) into v_length;
for v_pos in 1 .. v_length loop
--while v_pos <= v_length loop
SELECT case substring(p_num,v_pos,1)
when '1' then 'ONE '
when '2' then 'TWO '
when '3' then 'THREE '
when '4' then 'FOUR '
when '5' then 'FIVE '
when '6' then 'SIX '
when '7' then 'SEVEN '
when '8' then 'EIGHT '
when '9' then 'NINE '
when '0' then 'ZERO '
ELSE 'NULL'
end into v_word;
v_spell := v_spell || v_word;
--v_pos := v_pos+1;
end loop;
return v_spell;
end;
$$ language plpgsql
while execute this function it return output like
select fn_spellout('12345')
"ONE TWO THREE FOUR FIVE "
All about data processing & analytics. Open source database,No-SQL and Hadoop. Discussed issues/solution which I've got during my experience..
Subscribe to:
Post Comments (Atom)
Labels
- #agriculture (1)
- #ai (5)
- #pdf (1)
- Big Data (30)
- blogging (3)
- data analytics (5)
- data science (7)
- Deep Learning (4)
- Hadoop (28)
- Hadoop Eco System (27)
- hdfsCommands (4)
- Hive (5)
- IssueSolution (4)
- jobs (3)
- links (1)
- Machine Learning (4)
- mahout (2)
- MapReduce (1)
- MongoDB (6)
- MySQL (4)
- PlpgSQL (8)
- postgres (6)
- PostgreSQL (53)
- R (20)
- RHadoop (2)
- search keywords (1)
- social (2)
- spark (3)
- twitter (6)
- usecase (2)
- visualization (7)
- weka (1)
No comments:
Post a Comment