Re: Number Conversion Function

Abdul Rehman wrote:
> Can any body help me in converting numeric values into words
> via postgres function: i.e. 313 to THREE HUNDRED THREE

Here is a little PL/pgSQL function that should do what you
want for numbers < 1000000000000.
It should be easy to extend if you switch to bigint and decide
if you want British or American English.

I played with it, but it may still contain bugs.

Yours,
Laurenz Albe

CREATE OR REPLACE FUNCTION to_text(integer) RETURNS text LANGUAGE sql IMMUTABLE STRICT AS
$$SELECT CASE WHEN $1<1 THEN NULL
WHEN $1=1 THEN 'ONE'
WHEN $1=2 THEN 'TWO'
WHEN $1=3 THEN 'THREE'
WHEN $1=4 THEN 'FOUR'
WHEN $1=5 THEN 'FIVE'
WHEN $1=6 THEN 'SIX'
WHEN $1=7 THEN 'SEVEN'
WHEN $1=8 THEN 'EIGHT'
WHEN $1=9 THEN 'NINE'
WHEN $1=10 THEN 'TEN'
WHEN $1=11 THEN 'ELEVEN'
WHEN $1=12 THEN 'TWELVE'
WHEN $1=13 THEN 'THIRTEEN'
WHEN $1=14 THEN 'FOURTEEN'
WHEN $1=15 THEN 'FIFTEEN'
WHEN $1=16 THEN 'SIXTEEN'
WHEN $1=17 THEN 'SEVENTEEN'
WHEN $1=18 THEN 'EIGHTEEN'
WHEN $1=19 THEN 'NINETEEN'
WHEN $1<100 THEN CASE
WHEN $1/10=2 THEN 'TWENTY' || COALESCE(' ' || to_text($1%10), '')
WHEN $1/10=3 THEN 'THIRTY' || COALESCE(' ' || to_text($1%10), '')
WHEN $1/10=4 THEN 'FOURTY' || COALESCE(' ' || to_text($1%10), '')
WHEN $1/10=5 THEN 'FIFTY' || COALESCE(' ' || to_text($1%10), '')
WHEN $1/10=6 THEN 'SIXTY' || COALESCE(' ' || to_text($1%10), '')
WHEN $1/10=7 THEN 'SEVENTY' || COALESCE(' ' || to_text($1%10), '')
WHEN $1/10=8 THEN 'EIGHTY' || COALESCE(' ' || to_text($1%10), '')
WHEN $1/10=9 THEN 'NINETY' || COALESCE(' ' || to_text($1%10), '')
END
WHEN $1<1000
THEN to_text($1/100) || ' HUNDRED' ||
COALESCE(' AND ' || to_text($1%100), '')
WHEN $1<1000000
THEN to_text($1/1000) || ' THOUSAND' ||
CASE WHEN $1%1000 < 100
THEN COALESCE(' AND ' || to_text($1%1000), '')
ELSE COALESCE(' ' || to_text($1%1000), '')
END
WHEN $1<1000000000
THEN to_text($1/1000000) || ' MILLION' ||
CASE WHEN $1%1000000 < 100
THEN COALESCE(' AND ' || to_text($1%1000000), '')
ELSE COALESCE(' ' || to_text($1%1000000), '')
END
END$$;