How To Convert Number Into Words Using Oracle SQL Query

How can you convert a number into words using Oracle Sql Query? What I mean by Number to Word is:

12 = Twelve 102 = One Hundred Two 1020 = One Thousand Twenty

Here’s a classy query which will convert number into words.Please see the query below:

select to_char(to_date(:number,'j'),'jsp') from dual;

If I pass 234 in number, then the output will : two hundred thirty-four

SELECT TO_CHAR (TO_DATE (234, 'j'), 'jsp') FROM DUAL; //Output: two hundred thirty-four   SELECT TO_CHAR (TO_DATE (24834, 'j'), 'jsp') FROM DUAL; //Output: twenty-four thousand eight hundred thirty-four   SELECT TO_CHAR (TO_DATE (2447834, 'j'), 'jsp') FROM DUAL; //Output: two million four hundred forty-seven thousand eight hundred thirty-four

So how the query works? Well here’s why:

If you look into the inner most part of the query to_date(:number,'j') the ‘j’ or J is the Julian Date (January 1, 4713 BC), basically this date is been used for astronomical studies.

So to_date(:number,'j') it take the number represented by number and pretend it is a julian date, convert into a date.

If you pass 3 to number, so it will convert date to 3rd Jan 4713 BC, it means 3 is added to the Julian date.

Now to_char(to_date(:number,'j'),'jsp'), jsp = Now; take that date(to_date(:number,'j')) and spell the julian number it represents

Limitation & workaround

There is a limitation while using Julian dates ,It ranges from 1 to 5373484. That’s why if you put the values after 5373484, it will throw you an error as shown below:

ORA-01854: julian date must be between 1 and 5373484

To cater the above problem ,create a function ,and with little trick with j->jsp ,you can fetch the desired result.

CREATE OR REPLACE FUNCTION spell_number (p_number IN NUMBER)    RETURN VARCHAR2 AS    TYPE myArray IS TABLE OF VARCHAR2 (255);      l_str myArray          := myArray ('',                      ' thousand ',                      ' million ',                      ' billion ',                      ' trillion ',                      ' quadrillion ',                      ' quintillion ',                      ' sextillion ',                      ' septillion ',                      ' octillion ',                      ' nonillion ',                      ' decillion ',                      ' undecillion ',                      ' duodecillion ');      l_num      VARCHAR2 (50) DEFAULT TRUNC (p_number);    l_return   VARCHAR2 (4000); BEGIN    FOR i IN 1 .. l_str.COUNT    LOOP       EXIT WHEN l_num IS NULL;         IF (SUBSTR (l_num, LENGTH (l_num) - 2, 3) <> 0)       THEN          l_return :=             TO_CHAR (TO_DATE (SUBSTR (l_num, LENGTH (l_num) - 2, 3), 'J'),                      'Jsp')             || l_str (i)             || l_return;       END IF;         l_num := SUBSTR (l_num, 1, LENGTH (l_num) - 3);    END LOOP;      RETURN l_return; END; /     SELECT spell_number (53734555555585) FROM DUAL;

Output: ahmed hazzaf

Fifty-Three trillion Seven Hundred Thirty-Four billion Five Hundred Fifty-Five million Five Hundred Fifty-Five thousand Five Hundred Eighty-Five