Re: Number Conversion Function - Mailing list pgsql-general

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$$;