Home > Database > Oracle

How To Convert Number into Words using Oracle SQL Query

number 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:

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 :)

Related Posts

  1. Generating Random Data in Oracle
  2. Row Data Multiplication in Oracle
  3. Pagination in Oracle using ROWNUM and Limiting Result Set
  4. Invisible Indexes in Oracle 11g
  5. Fetch Random rows from Database (MySQL, Oracle, MS SQL, PostgreSQL)
  6. Oracle 11G new feature: Virtual Column
  7. Deleting Duplicate Rows in Oracle


Tags: , , , ,

19 Comments

  • Atul Goyal 29 May, 2012, 17:51

    When i am running this query i see the below error

    [SQL] SELECT TO_CHAR (TO_DATE (24834, ‘j’), ‘jsp’) FROM DUAL;
    [Err] 1305 – TO_CHAR does not exist

    Reply
    • Viral Patel 29 May, 2012, 18:28

      Hi Atul, Check the output for SELECT TO_DATE (24834, ‘j’) FROM DUAL. Can you tell me what output you getting for this query?

      Reply
    • Raj 15 March, 2013, 13:42

      SELECT TO_CHAR (TO_DATE (24834, ‘j’), ‘jsp’) FROM DUAL

      Dear Atul, copy this one and try..u will get result.

      Reply
      • Bilal 30 April, 2013, 13:31

        Yes thankyou..this one is working…………

        Reply
  • Abhinav Kulshreshtha 30 May, 2012, 14:41

    Nice and Handy technique.. Found a good thing on your rss feed after quite some time.
    I Guess a very handful of people knew about it.

    Is it Oracle only trick or is it valid for any SQL (like MySQL or SQLite) ?

    Reply
    • Viral Patel 30 May, 2012, 15:12

      Hi, The functions that we used here to_char & to_date are oracle only. I am sure there might be similar methods with other DBs.

      Reply
  • Srini 5 June, 2012, 13:12

    the max it can fetch is 1 million. is there any way to get more. just curious..

    Reply
    • Viral Patel 5 June, 2012, 14:14

      Srini, That’s the limit of Julian date. I have updated the post and added a workaround for that problem. Have a look.

      Reply
  • Abhinav Kulshreshtha 5 June, 2012, 14:41

    Hi..
    I tried to deduce a MySQL version for this trick. There is a STR_TO_DATE and DATE_FORMAT as to_date() but couldn’t find a to_char() equivalent.

    I tried several manipulation from http://www.ispirer.com/doc/sqlways/Output/SQLWays-1-073.html
    but either i get a NULL or a error.

    Can you help me to get this trick done on a MySQL DB.

    Reply
  • Sridhar 12 June, 2012, 12:20

    Hi,
    One other limitation that needs to be considered is that this code won’t work if the NLS_CALENDAR is not set to Gregorian:
    alter session set nls_calendar = ‘arabic hijrah’;

    SELECT TO_CHAR (TO_DATE (19, 'j'), 'jsp') FROM DUAL;
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

    The code needs to altered as below to fix the issue:

    SELECT TO_CHAR (TO_DATE (19, 'j','nls_calendar = ''gregorian'''), 'jsp','nls_calendar=''gregorian''') FROM DUAL
    Reply
  • How to use the created spell_number Function in Oracle Form 6i 18 October, 2012, 19:02

    That’s Great buddy,
    but how we can utilitse this function on the oracle 6i forms to show the result on Dispaly Item.
    ?????????????

    Reply
  • How to use the created spell_number Function in Oracle Form 6i 18 October, 2012, 19:04

    I tried but not worked.

    Reply
  • Gaurav 19 October, 2012, 10:48

    how to get output if we have digit like 223.32 (means in decimal)

    Reply
    • Gaurav 19 October, 2012, 11:58

      select to_char(to_date(floor(1234.99),’J'),’Jsp’)||’ and ‘||
      to_char(to_date((1234.99-(floor(1234.99)))*100,’J'),’Jsp’)|| ‘ paise’ from dual

      Reply
  • ravikant pawar 29 October, 2012, 10:28

    If any one wants a single sql query to get this done please follow the below. It will show you till trillion amount but you can modify the same.
    A decimal number to word conversion.

    SELECT                                                  
A.LCY_AMOUNT,                                                 
upper(case when length(floor(A.LCY_AMOUNT))&gt;12 then TO_CHAR (TO_DATE (floor(floor(A.LCY_AMOUNT)/1000000000000), 'j'), 'jsp')
||' TRILLION '||DECODE(substr(floor(A.LCY_AMOUNT),length(floor(A.LCY_AMOUNT))-11,3), 0, '',(TO_CHAR(TO_DATE(to_number(substr(floor(A.LCY_AMOUNT),length(floor(A.LCY_AMOUNT))-11,3)),'J'),'JSP'))||' BILLION ')
||DECODE(substr(floor(A.LCY_AMOUNT),length(floor(A.LCY_AMOUNT))-8,3), 0, '',(TO_CHAR(TO_DATE(to_number(substr(floor(A.LCY_AMOUNT),length(floor(A.LCY_AMOUNT))-8,3)),'J'),'JSP'))||' MILLION ')
||DECODE(substr(floor(A.LCY_AMOUNT),length(floor(A.LCY_AMOUNT))-5), 0, '',(TO_CHAR(TO_DATE(substr(floor(A.LCY_AMOUNT),length(floor(A.LCY_AMOUNT))-5),'J'),'JSP'))) 
||decode((A.LCY_AMOUNT-floor(A.LCY_AMOUNT))*100,0,'',' AND '||TO_CHAR (TO_DATE ((A.LCY_AMOUNT-floor(A.LCY_AMOUNT))*100, 'j'), 'jsp')||' CENTS')
when length(floor(A.LCY_AMOUNT))&gt;9 then TO_CHAR (TO_DATE (floor(floor(A.LCY_AMOUNT)/1000000000), 'j'), 'jsp')
||' BILLION '||DECODE(substr(floor(A.LCY_AMOUNT),length(floor(A.LCY_AMOUNT))-8,3), 0, '',(TO_CHAR(TO_DATE(to_number(substr(floor(A.LCY_AMOUNT),length(floor(A.LCY_AMOUNT))-8,3)),'J'),'JSP'))||' MILLION ')
||DECODE(substr(floor(A.LCY_AMOUNT),length(floor(A.LCY_AMOUNT))-5), 0, '',(TO_CHAR(TO_DATE(substr(floor(A.LCY_AMOUNT),length(floor(A.LCY_AMOUNT))-5),'J'),'JSP')))
||decode((A.LCY_AMOUNT-floor(A.LCY_AMOUNT))*100,0,'',' AND '||TO_CHAR (TO_DATE ((A.LCY_AMOUNT-floor(A.LCY_AMOUNT))*100, 'j'), 'jsp')||' CENTS')
when length(floor(A.LCY_AMOUNT))&gt;=7 then TO_CHAR (TO_DATE (floor(floor(A.LCY_AMOUNT)/1000000), 'j'), 'jsp')
||' MILLION '||DECODE(substr(floor(A.LCY_AMOUNT),length(floor(A.LCY_AMOUNT))-5), 0, '',(TO_CHAR(TO_DATE(substr(floor(A.LCY_AMOUNT),length(floor(A.LCY_AMOUNT))-5),'J'),'JSP')))
||decode((A.LCY_AMOUNT-floor(A.LCY_AMOUNT))*100,0,'',' AND '||TO_CHAR (TO_DATE ((A.LCY_AMOUNT-floor(A.LCY_AMOUNT))*100, 'j'), 'jsp')||' CENTS')
else decode( (floor(A.LCY_AMOUNT)),0,'', ((TO_CHAR(TO_DATE((floor(A.LCY_AMOUNT)),'J'),'JSP'))))
||decode((A.LCY_AMOUNT-floor(A.LCY_AMOUNT))*100,0,'',' AND '||TO_CHAR (TO_DATE ((A.LCY_AMOUNT-floor(A.LCY_AMOUNT))*100, 'j'), 'jsp')||' CENTS')
end ) Amount
FROM ACVWS_ALL_AC_ENTRIES a
    Reply
  • Uthaya 18 December, 2012, 16:46

    Hi friends kindly help me to
    How to convert in to lakhs and cores ??
    i dont need in million & billion …

    Reply
  • khyber khan 4 January, 2013, 9:29

    Hi.

    I want to convert a given date in words e,g 14/01/1912 to Fourteen January Nineteen hundred and twelve.

    can any one help me

    Reply
  • khyber khan 4 January, 2013, 9:30

    Hi.

    I want to convert a given date in words e,g 14/01/1912 to Fourteen January Nineteen hundred and twelve using oracle

    can any one help me

    Reply
  • Anand 1 March, 2013, 15:54

    Query For use fractional part as string like 1.10 will display one point ten ????

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Note

To post source code in comment, use [code language] [/code] tag, for example:

  • [code java] Java source code here [/code]
  • [code html] HTML here [/code]