How to convert number into word/text with PL/SQL
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')
It will return
1/11/4712Since, 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
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')
It will return:
One Million Two Hundred Thirty-Four Thousand Five Hundred Sixty-SevenSo 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)
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.
v_number := SUBSTR (v_number, 1, LENGTH (v_number) - 3);
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-EightI 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