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



24 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

    • 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?

    • 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.

      • Bilal 30 April, 2013, 13:31

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

  • 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) ?

    • 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.

  • Srini 5 June, 2012, 13:12

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

    • 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.

  • 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.

  • 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
    
  • 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.
    ?????????????

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

    I tried but not worked.

  • Gaurav 19 October, 2012, 10:48

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

    • 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

  • 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
    
    • Rajkumar 15 November, 2013, 20:57

      Can any one help me to get the results in indian format…like Crore and Lack and Thousands and Tens

  • 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 …

  • 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

  • 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

  • Anand 1 March, 2013, 15:54

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

  • madhavi 6 July, 2013, 14:39

    i have one query , when i execute the query it gives the this error
    ORA-01830: date format picture ends before converting entire input string
    the query

    SELECT NI.POLICY_HOLDER_SALUTATION || ‘ ‘ ||PD.DRAWER_NAME AS DRAWER_NAME,
    PD.ADDRESS_LINE_1 || NVL2(PD.ADDRESS_LINE_1,’,’,”) || PD.ADDRESS_LINE_2 || NVL2(PD.ADDRESS_LINE_2,’,’,”) || PD.ADDRESS_LINE_3 || NVL2(PD.ADDRESS_LINE_3,’,’,”) || PD.CITY || NVL2(PD.CITY,’,’,”) || PD.PINCODE || NVL2(PD.PINCODE,’,’,”) || CHR(10) || PD.STATE AS DRAWER_ADDRESS,
    PD.DRAWER_CONTACT_NO AS DRAWER_CONTACT_NO,
    TO_CHAR(PD.LOGIN_DATE,’DD-Mon-YYYY’) AS LOGIN_DATE ,
    NVL((CASE WHEN PAYMENT_MODE IN (‘Cheque’,’Demand Draft’) THEN TO_CHAR(PD.INSTRUMENT_DATE,’DD-Mon-YYYY’)
    WHEN PAYMENT_MODE IN (‘Cash’,’OTC Yes Bank’) THEN ”
    ELSE TO_CHAR(PD.LOGIN_DATE,’DD-Mon-YYYY’) END),’ ‘) AS INSTRUMENT_DATE,
    PD.USER_BRANCH_NAME,
    PD.RECEIPT_NUMBER,
    TO_CHAR(PD.AMOUNT,’999999999.99′) AS AMOUNT,
    NVL(PD.POLICY_STATUS,’ ‘) POLICY_STATUS,
    NVL((CASE WHEN PD.PAYMENT_MODE IN (‘Cash’,’OTC Yes Bank’) THEN NULL ELSE PD.INSTRUMENT_NUMBER END),’ ‘) AS INSTRUMENT_NUMBER,
    NVL(PD.DRAWER_BANK,’ ‘) AS DRAWER_BANK,
    PD.CONTRACT_NUMBER,
    NVL(PD.EMAIL_ID,’ ‘) EMAIL_ID,
    NVL(PD.PLAN_NAME,’ ‘) PLAN_NAME,
    NVL(PD.PREMIUM_FREQUENCY,’ ‘) PREMIUM_FREQUENCY,
    TO_CHAR(PD.INSTRUMENT_DATE,’DD-Mon-YYYY’),
    PD.CHEQUE_RECEIVE_DATE, TRIM(f_words(PD.AMOUNT)) AS AMOUNT_WORDS_OLD,
    UPPER((TO_CHAR (TO_DATE (PD.AMOUNT, ‘j’), ‘jsp’) || ‘ ‘ || ‘only’)) AS AMOUNT_WORDS,
    TO_NUMBER(PD.AMOUNT,’999999999.99′) AMOUNT1,TRIM(TO_CHAR(PD.AMOUNT,’999,999,999.99′)) AMOUNT2,NVL(TO_CHAR(PD.RISK_COMMENCE_DATE,’DD-Mon-YYYY’),’ ‘) AS RISK_COMMENCE_DATE,
    PD.PAYMENT_MODE AS PAYMENT_METHOD
    FROM PAYMENT_DETAILS PD,NEWBUSS.NB_INWARD@NB_CCS NI WHERE PD.CONTRACT_NUMBER=NI.APPLICATION_ID AND PD.RECEIPT_NUMBER=$P{RECEIPT_NUMBER}

  • vinoth 8 November, 2013, 17:16

    i am using this query to convert for this number 1224834
    its return for :
    one million two hundred twenty-four thousand eight hundred thirty-four

    but i want result like :
    Twelve Lakhs Twenty Four Thousand Eight Hundred And Thirty Four Only
    any ideas ?

    • Rajkumar 16 November, 2013, 12:07

      CREATE OR REPLACE FUNCTION APPS.num_to_word (p_num2 IN NUMBER)
      RETURN VARCHAR2
      IS
      p_num1 NUMBER;
      p_round NUMBER;
      p_num NUMBER;
      p_scale NUMBER;
      p_s_word VARCHAR2 (100);
      v_num_word VARCHAR2 (2000);
      l_num NUMBER;
      v_word VARCHAR2 (2000);
      v_word1 VARCHAR2 (2000);
      v_word2 VARCHAR2 (2000);
      v_word3 VARCHAR2 (2000);
      BEGIN
      p_num1 := ABS (p_num2);
      p_round := ROUND (p_num1, 5);

      SELECT SUBSTR (p_num1,
      1,
      DECODE (INSTR (p_num1, ‘.’) – 1,
      -1, LENGTH (p_num1),
      INSTR (p_num1, ‘.’) – 1
      )
      )
      INTO p_num
      FROM DUAL;

      IF INSTR (p_num1, ‘.’) > 0
      THEN
      p_scale := SUBSTR (p_round, INSTR (p_round, ‘.’) + 1);

      IF LENGTH(p_scale)=1 THEN
      SELECT TO_CHAR (TO_DATE (p_scale||’0′, ‘j’), ‘jsp’)
      INTO p_s_word
      FROM DUAL;
      ELSE
      SELECT TO_CHAR (TO_DATE (p_scale, ‘j’), ‘jsp’)
      INTO p_s_word
      FROM DUAL;
      end if;

      END IF;

      l_num := LENGTH (p_num);

      IF l_num 5 AND l_num = 8
      THEN
      SELECT TO_CHAR (TO_DATE (SUBSTR (p_num, 1, l_num – 7), ‘j’), ‘jsp’)
      INTO v_word1
      FROM DUAL;

      IF SUBSTR (p_num, l_num – 7 + 1, 2) > 0
      THEN
      SELECT TO_CHAR (TO_DATE (SUBSTR (p_num, l_num – 7 + 1, 2), ‘j’),
      ‘jsp’
      )
      INTO v_word2
      FROM DUAL;
      END IF;

      SELECT TO_CHAR (TO_DATE (LTRIM (SUBSTR (p_num, l_num – 5 + 1), 0), ‘j’),
      ‘jsp’
      )
      INTO v_word3
      FROM DUAL;

      IF SUBSTR (p_num, l_num – 7 + 1, 2) > 0
      THEN
      v_word := v_word1 || ‘ crore ‘ || v_word2 || ‘ lakh ‘ || v_word3;
      ELSE
      v_word := v_word1 || ‘ crore ‘ || v_word3;
      END IF;
      END IF;
      IF INSTR (p_num1, ‘.’) > 0
      THEN
      v_num_word :=
      INITCAP (‘Rs. ‘ || v_word || ‘ and ‘ || p_s_word || ‘ paisa only.’
      );
      ELSE
      v_num_word := INITCAP (‘Rs. ‘ || v_word || ‘ only.’);
      END IF;

      IF SIGN (p_num2) = -1
      THEN
      RETURN ‘-’ || v_num_word;
      ELSE
      RETURN v_num_word;
      END IF;

      END;
      /

  • Apollo 13 April, 2014, 15:18

    You people don’t understand the beauty of the small code thats why I am seeing so many complex functions after such a nice and handy explanation.

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]

Current day month ye@r *