Search This Blog

Friday, September 26, 2014

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

How to convert number to word/text with PL/SQL:Easy Method

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


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

It will return


. 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')

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

It will through and error ;

Julian date 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.

[+] [-]  Show/Hide Code

  • CREATE OR REPLACE FUNCTION fnc_spell_number (p_number IN NUMBER)
    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);

    FOR i IN 1 .. arrary_element.COUNT

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

    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

    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);
    RETURN v_word;

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)

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.