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 ThirtyFour Thousand Five Hundred SixtySeven
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
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 FortyFive Thousand Six Hundred SeventyEight
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.
No comments:
Post a Comment
Thank you for your valuable comments!