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

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

---