How to convert number into words using SQL in Oracle ?

Conversion of number into words mostly required in amount translation. In this article we will see how to do this conversion in SQL.

How to do ?

Oracle has a Julian Date format which converts a number into Julian year and then Julian year can be used to spell the desired value.

Suppose I want to convert 12345 into words. Below is the query to get this number into words.

SELECT TO_CHAR(TO_DATE(12345,'J'),'JSP') IN_WORDS FROM DUAL;

IN_WORDS
---------------
TWELVE THOUSAND THREE HUNDRED FORTY-FIVE

How it works ?

In the above query

  1. ‘J’ is used for converting a specific integer into Julian Date.
  2. ‘JSP’ stands for Julian Spelling with all words in upper case. We can use ‘jsp’ for all small case and ‘Jsp’ for initial capital case.

Limitations of using Julian Date :

  1. We can use Julian spelling for integer values only. In case of decimal numbers we need to divide the number into two parts before and after decimal and then apply the Julian spelling separately.
  2. Using Julian Spelling we can spell the the desired value in the range of 1 to 5373484 with 1 as Minimum value and 5373484 as Maximum value.

How to overcome these Limitations ?

How to handle conversion of Decimal numbers using Julian Date:

  1. In case of decimal numbers we need to divide that number into two parts before and after decimal and we need to apply Julian spelling separately.
  2. Suppose we want to spell for number 12345.67. As stated above, we need to divide this number before and after decimal point.
  3. For getting number before decimal point, we can simply applyTRUNC() function to the number.
  4. For getting number after decimal point, we can either applyMOD() function or we can useSUBSTR() and INSTR() functions.

Below are the queries for this conversion. both queries gives the same result.

SELECT 
    TO_CHAR(TO_DATE(TRUNC(12345.67),'J'),'JSP') 
    || ' AND ' || 
    TO_CHAR(TO_DATE(TO_NUMBER(MOD(12345.67,1)*100),'J'),'JSP') IN_WORDS
FROM DUAL;

IN_WORDS
----------------------
TWELVE THOUSAND THREE HUNDRED FORTY-FIVE AND SIXTY-SEVEN

SELECT
    TO_CHAR(TO_DATE(TRUNC(12345.67),'J'),'JSP') 
    || ' AND ' || 
    TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(12345.67,INSTR(12345.67,'.')+1)),'J'),'JSP')
FROM DUAL;

IN_WORDS
----------------------
TWELVE THOUSAND THREE HUNDRED FORTY-FIVE AND SIXTY-SEVEN

How to handle Limit of Integers in Julian Date :

As stated above, using Julian Spelling we can spell the the desired value in the range of 1 to 5373484 with 1 as Minimum value and 5373484 as Maximum value. Now question is that how to remove this limitation. For this, we can use the below function. This function is very easy to read and understand.

CREATE OR REPLACE FUNCTION NUMBER_TO_WORDS
(
P_NUMBER IN NUMBER
)
RETURN VARCHAR2
IS
--ARRAY FOR TRANSLATING THE NUMBER TO THE CORRECT SPECIFIER
TYPE WORDS_ARRAY IS TABLE OF VARCHAR2(500);
WORD_STRING        WORDS_ARRAY := WORDS_ARRAY('',
                           ' THOUSAND ', ' MILLION ',
                           ' BILLION ', ' TRILLION ',
                           ' QUADRILLION ', ' QUINTILLION ',
                           ' SEXTILLION ', ' SEPTILLION ',
                           ' OCTILLION ', ' NONILLION ',
                           ' DECILLION ', ' UNDECILLION ',
                           ' DUODECILLION ');

V_NUMBER        VARCHAR2(50) DEFAULT TRUNC(P_NUMBER);
V_NUMBER2        VARCHAR2(50);
V_DECIMAL        VARCHAR2(10);
V_RETURN_STR    VARCHAR2(4000);

BEGIN
    --CHECKING FOR DECIMAL POINT IN THE NUMBER
    V_DECIMAL := INSTR(P_NUMBER,'.');

    --IF DECIMAL POINT THEN GET THE VALUE AFTER DECIMAL POINT
    IF V_DECIMAL <> '0' THEN    
        V_NUMBER2 := SUBSTR(P_NUMBER,INSTR(P_NUMBER,'.')+1);    
    ELSE    
        V_NUMBER2 := NULL;    
    END IF;

    FOR I IN 1 .. WORD_STRING.COUNT
    LOOP
        EXIT WHEN V_NUMBER IS NULL;

        /*HERE WE ARE PARSING THE NUMBER LESS THAN 1000 UNTIL 
	THERE IS NO MORE NUMBER TO PARSE AND TRANSLATE IT USING JULIAN SPELLING.
	WE ARE CONCATENATING THE TRANSLATED VALUES TO THE OUTPUT 
	STRING USING THE VALUES DEFINED IN THE ARRAY ABOVE.*/           

        V_RETURN_STR := TO_CHAR(TO_DATE(
                                    SUBSTR(V_NUMBER,LENGTH(V_NUMBER)-2,3),'J'),
                                    'JSP') || WORD_STRING(I) || V_RETURN_STR;

        --REMOVING THE DIGITS WE JUST PROCESSED AND CONTINUE.                            
        V_NUMBER := SUBSTR(V_NUMBER,1,LENGTH(V_NUMBER)-3);                                                

    END LOOP;
    --IF NUMBER AFTER DECIMAL POINT IS AVAILABLE THEN CONCATENATING THIS WITH OUTPUT STRING.
    IF V_NUMBER2 IS NOT NULL THEN

        V_RETURN_STR := V_RETURN_STR || ' AND ' ||TO_CHAR(TO_DATE(V_NUMBER2,'J'),'JSP');

    END IF;

    RETURN V_RETURN_STR;
END;

The above function produces the following results:

SELECT NUMBER_TO_WORDS(12345) IN_WORDS FROM DUAL;

IN_WORDS
------------------------
TWELVE THOUSAND THREE HUNDRED FORTY-FIVE

SELECT NUMBER_TO_WORDS(5373485.45) IN_WORDS FROM DUAL;

IN_WORDS
------------------------
FIVE MILLION THREE HUNDRED SEVENTY-THREE THOUSAND FOUR HUNDRED EIGHTY-FIVE AND FORTY-FIVE

So, we learned how to convert number into words using sql in oracle.

How to convert number into words using SQL in Oracle ?

5 thoughts on “How to convert number into words using SQL in Oracle ?

  • March 3, 2015 at 5:02 pm
    Permalink

    All is good.
    when number is: 34.34
    word must be: Thirty Four point Three Four(Not Thirty Four point Thirty Four)

    when number is:34.04
    word must be: Thirty Four point Zero Four (Not Thirty Four point Four)

    Thanks

    Reply
  • September 26, 2015 at 8:27 am
    Permalink

    we can extend the limit by using case, so no need to create a function.

    Reply
  • September 26, 2015 at 4:20 pm
    Permalink

    With T As
    (
    select 9999999999 N from dual
    )
    select case
    when length(n)<6 then to_char(to_date(n,'J'),'JSP')
    when length(n)=6 then to_char(to_date(substr(n,1,1),'J'),'JSP')||' LAKH ' ||to_char(to_date(substr(n,2),'J'),'JSP')
    when length(n)=7 then to_char(to_date(substr(n,1,2),'J'),'JSP')||' LAKH ' ||to_char(to_date(substr(n,3),'J'),'JSP')
    when length(n)=8 then to_char(to_date(substr(n,1,1),'J'),'JSP')||' CRORE ' || to_char(to_date(substr(n,2,2),'J'),'JSP')||' LAKH ' ||to_char(to_date(substr(n,4),'J'),'JSP')
    when length(n)=9 then to_char(to_date(substr(n,1,2),'J'),'JSP')||' CRORE ' || to_char(to_date(substr(n,3,2),'J'),'JSP')||' LAKH ' ||to_char(to_date(substr(n,5),'J'),'JSP')
    when length(n)=10 then
    to_char(to_date(substr(n,1,1),'J'),'JSP')||' ARAB ' || to_char(to_date(substr(n,2,2),'J'),'JSP')||' CRORE ' || to_char(to_date(substr(n,4,2),'J'),'JSP')||' LAKH ' ||to_char(to_date(substr(n,6),'J'),'JSP')
    else
    'LIMIT CROSSED – MAXIMUM 10 DIGIT ARE ALLOWED'
    end IN_WORD
    from T ;

    Reply
    • September 26, 2015 at 4:52 pm
      Permalink

      Hi Anurag,
      Thanks for your comment. We can do in this way also. Need to modified your query for decimal numbers. As function provides us re-usability of code we should go for it. After all it depends on application requirement.

      Reply

Leave a Reply