SQL & PL/SQL

Frequently Asked Question (FAQ):
How do I spell a number or convert a number to words.

Answer:
A common method for spelling a number or converting a number to words is to use the TO_DATE function and 'j' format to convert the number to a julian date (the number of days since January 1, 4712 B.C.), then use the TO_CHAR function and 'jsp' format to spell the julian date. This is commonly used in automated check-printing programs, especially payroll.

Caveat emptor:
This posting is the work of the Rogue Moderators. It is posted with the best of intentions but is not guaranteed in any way, shape or form. In particular, the code is presented "as is" and you assume full responsibility for running it on your system.

Example:
SQL> SELECT TO_CHAR (TO_DATE (1234567, 'j'), 'jsp') FROM DUAL
2 /

TO_CHAR(TO_DATE(1234567,'J'),'JSP')
---------------------------------------------------------------------
one million two hundred thirty-four thousand five hundred sixty-seven

1 row selected.

The same method can also be used in a function:
To create the function:
SQL> CREATE OR REPLACE FUNCTION number_to_words
2 (p_number IN NUMBER)
3 RETURN VARCHAR2
4 AS
5 BEGIN
6 RETURN TO_CHAR (TO_DATE (p_number, 'j'), 'jsp');
7 END number_to_words;
8 /

Function created.

To use the function:
SQL> SELECT number_to_words (1234567) FROM DUAL
2 /

NUMBER_TO_WORDS(1234567)
-------------------------------------------------------------------------------
one million two hundred thirty-four thousand five hundred sixty-seven

1 row selected.

If you wish to spell numbers greater than the maximum julian date allowed (5373484) or if you wish to include decimal places, or if you wish to have the output in another format or language, then you will need a more complex function. Click on the link below for a function by Tom Kyte that works for larger numbers, then scroll down for modifications by me that include decimal places, other formats, and other languages:


http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1407603857650