Hosting Service

19 Sep 2014 | MAK Azad

How to convert number into word/text with PL/SQL

Category : Oracle | Tag : PL/SQL | Number to in-words |

Today I’ll discus on how we can convert number into word easly. The easiest way is to use ‘JSP’ format mask of Julian date. Here ‘J’ for Julian day. Julian day is the number of day from 1 January, 4712 B.C and ‘SP’ for spell. Now we will see some example to be clear about Julian date. If we write the SQL query like below

SELECT TO_DATE (11, 'j')
      FROM DUAL;

It will return

1/11/4712

Since, Julian day is the number of day from 1 January, 4712 B.C so it added 11 days with 1 January, 4712 and return result 11 January,4712. Now we will use ‘Jsp’ format mask to spell the number 11.

SELECT TO_CHAR (TO_DATE (11, 'j'), 'Jsp')
      FROM DUAL

 

It will return

Eleven

. In this way we can convert number to word/text. Now we will see some more examples.

 

SELECT TO_CHAR (TO_DATE (1234567, 'j'), 'Jsp')
      FROM DUAL

It will return:

One Million Two Hundred Thirty-Four Thousand Five Hundred Sixty-Seven

So easy and nice to convert number into word/text! But If we increase one more digit what will happen?

SELECT TO_CHAR (TO_DATE (12345678, 'j'), 'Jsp')
      FROM DUALFROM DUAL

It will through an error ;

What happen? Why it can’t spell this number? Because of there is a limitation of Julian date, it’s ranges from 1 to 5373484. After this number it can’t do anything. So what can we do? We can resolve the issue very easily with little tricks.

Here is a function which can cater this problem and can spell out any number you wish.This function will spell number in metric system.


  • CREATE OR REPLACE FUNCTION fnc_spell_number (p_number IN NUMBER)
    RETURN VARCHAR2
    AS
    TYPE myarray IS TABLE OF VARCHAR2 (255);

    --Declaring a Oracle Associative ARRAY type to hold string

    arrary_element myarray := myarray
          (       '', ' Thousand ', ' Million ', ' Billion ', ' Trillion ', ' Quadrillion ',' Quintillion ', ' Sextillion ', ' Septillion ',' Octillion ', ' Nonillion ', ' Decillion ', ' Undecillion ', ' Duodecillion '
          );

    --Initializing the ARRAY

    v_number VARCHAR2 (50) DEFAULT TRUNC (p_number);
    v_word VARCHAR2 (4000);

    BEGIN
    FOR i IN 1 .. arrary_element.COUNT

    --This loop will iterate up to, how many element in array

    LOOP
    EXIT WHEN v_number IS NULL;
    IF (SUBSTR (v_number, LENGTH (v_number) - 2, 3) <> 0)

    --it will cut the last three digit of the number, every time

    THEN
    v_word := TO_CHAR (TO_DATE (SUBSTR (v_number, LENGTH (v_number) - 2, 3), 'J'), 'Jsp') || arrary_element (i) || v_word;

    --arrary_element (i) will added the text from array. First time it
    --will added null 2nd time it will added thousand then million then
    --billion and so on.

    END IF;
    v_number := SUBSTR (v_number, 1, LENGTH (v_number) - 3);
    END LOOP;
    RETURN v_word;
    END;

Now if we test this function with the number 12345678 or any other number it will return value up to ‘DUODECILLION’;

SELECT fnc_spell_number (12345678)
     FROM DUAL;

Now it can return:

Twelve Million Three Hundred Forty-Five Thousand Six Hundred Seventy-Eight

I hope it will help you to understand how we can convert number into word/text.
If you have any query\comment please leave it in comment box. I'll be very pleased to reply you. Thank you.
 

All post under category Oracle


1. How to convert number into word/text with PL/SQL
2. An Overview and Clear Understanding of Oracle REF CURSOR
3. How to query/fetch data of MS Access DB from an Oracle DB?
4. How to install Apex5 with Oracle REST Data Services in windows
5. How to Restore Deleted Rows in Oracle | Data Recovery
6. How to run an oracle job from windows batch file
7. How to convert | export oracle matrix report into M.S Excel file, oracle 11g