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