Thursday, September 23, 2010

Function for spell out a number

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 "

No comments: