Integer to Text

Library Snippets

Integer to Text

Works with PostgreSQL

any version

Written in

SQL

Depends on

Nothing

This function converts an integer between 1 and 999999999 to uppercase English text:

CREATE 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
              ELSE NULL
         END$$;