Integer to Text

From PostgreSQL wiki
Jump to navigationJump to search

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