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.
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.
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;
Fifty-Three trillion Seven Hundred Thirty-Four billion Five Hundred Fifty-Five million Five Hundred Fifty-Five thousand Five Hundred Eighty-Five
Hope this helps :)