Spell Number : Convert Number INTO Words
How can you convert a number into words using Oracle Sql Query?
How can I spell number?
Means:
12 = Twelve
102 = One Hundred Two
1020 = One Thousand Twenty
12.12 = twelve point twelve
Here’s a classy query which will convert number into words but this technique is havinglimitations which is discussed later in this topic .Please see the query below:
select to_char(to_date(:number,'j'),'jsp') from dual;
If I pass 134 in number, then the output will : one hundred thirty-four
SELECT TO_CHAR (TO_DATE (134, 'j'), 'jsp') FROM DUAL;
//Output: one hundred thirty-four
SELECT TO_CHAR (TO_DATE (34835, 'j'), 'jsp') FROM DUAL;
//Output: thirty-four thousand eight hundred thirty-five
SELECT TO_CHAR (TO_DATE (3447837, 'j'), 'jsp') FROM DUAL;
//Output: three million four hundred forty-seven thousand eight hundred thirty-seven
Understanding:
So how the query works?
If you look into the inner most part of the query to_date(:number,'j') the ‘j’ or J is the Julian Date(January 1, 4713 BC), basically this date is been used for astronomical studies.
So to_date(:number,'j') it take the number represented by number and pretend it is a julian date, convert into a date.
If you pass 3 to number, so it will convert date to 3rd Jan 4713 BC, it means 3 is added to the Julian date.
Now to_char(to_date(:number,'j'),'jsp'), jsp = Now; take that date(to_date(:number,'j')) and spell the julian number it represents
Limitation & workaround
There is a limitation while using Julian dates ,It ranges from 1 to 5373484. That’s why if you put the values after 5373484, it will throw you an error as shown below:
ORA-01854: julian date must be between 1 and 5373484
To cater the above problem ,create a function ,and with little trick with j->jsp ,you can fetch the desired result.
CREATE OR REPLACE FUNCTION spell_number (p_number IN NUMBER)
RETURN VARCHAR2
AS
TYPE myArray IS TABLE OF VARCHAR2 (255);
l_str myArray
:= myArray ('',
' thousand ',
' million ',
' billion ',
' trillion ',
' quadrillion ',
' quintillion ',
' sextillion ',
' septillion ',
' octillion ',
' nonillion ',
' decillion ',
' undecillion ',
' duodecillion ');
l_num VARCHAR2 (50) DEFAULT TRUNC (p_number);
l_return VARCHAR2 (4000);
BEGIN
FOR i IN 1 .. l_str.COUNT
LOOP
EXIT WHEN l_num IS NULL;
IF (SUBSTR (l_num, LENGTH (l_num) - 2, 3) <> 0)
THEN
l_return :=
TO_CHAR (TO_DATE (SUBSTR (l_num, LENGTH (l_num) - 2, 3), 'J'),
'Jsp')
|| l_str (i)
|| l_return;
END IF;
l_num := SUBSTR (l_num, 1, LENGTH (l_num) - 3);
END LOOP;
RETURN l_return;
END;
/
SELECT spell_number (12345678904321) FROM DUAL;
Output:
Twelve trillion Three Hundred Forty-Five billion Six Hundred Seventy-Eight million Nine Hundred Four thousand Three Hundred Twenty-One
Limitation
Limitation of this function is decimal figure. This function is not catering decimal figures.
So I am writing simple program to cater decimal figure but It ranges from 1 to 5373484. L anyways later In this article I have shared the program which will cater decimal figures too.
CREATE OR REPLACE FUNCTION spell_number(P_NUMBER IN NUMBER) RETURN VARCHAR IS
WORDS VARCHAR2(2000);
BEGIN
IF INSTR(P_NUMBER,'.') <> 0 THEN
SELECT INITCAP ((TO_CHAR (TO_DATE (SUBSTR (round(to_number(P_NUMBER),2), 1,
INSTR (P_NUMBER, '.', 1) - 1),
'J'
),
'JSP'
)
)
)
|| ' Saudi Riyals '||'and '
|| INITCAP ((TO_CHAR (TO_DATE (SUBSTR (round(to_number(P_NUMBER),2), INSTR(P_NUMBER, '.', 1) + 1),
'J'
),
'JSP'
)
)
)
|| ' Halalas' Curr into WORDS
FROM DUAL;
ELSE
SELECT INITCAP ((TO_CHAR (TO_DATE (P_NUMBER,'J'),
'JSP'
)
)
)
|| ' Saudi Riyals and 00 Halalas'
Curr into WORDS
FROM DUAL;
END IF;
RETURN WORDS;
Exception when others then return '00 Saudi Riyals and 00 Halalas';
END;
/
Examples
select test_convert_num_to_words(0) from dual
//output 00 Saudi Riyals and 00 Halalas
select test_convert_num_to_words(10) from dual
//output Ten Saudi Riyals and 00 Halalas
select test_convert_num_to_words(10.12) from dual
//output Ten Saudi Riyals andTwelve Halalas
select test_convert_num_to_words(10.129) from dual
//output Ten Saudi Riyals and Thirteen Halalas
This function rounded the figure up to two decimal places
Now I am going to share the function which will cater decimal figure as well but its limitation will be 1 to999999999999.
CREATE OR REPLACE FUNCTION APPS.g5ps_spell_number(in_char varchar2) RETURN varchar2 IS
--
-- *********************************************************************************
-- Function : Convert number to spell *
-- Usage : f_num_spelled('34567.88') *
-- *********************************************************************************
--
sub_char varchar2(100);
amt_with_cents number(15,2);
dollars varchar2(100);
len number(2);
c_num number(18) := to_number(in_char) ;
cents varchar2(100) := null ;
full_amt varchar2(100);
function F_SUB_1000 (in_char varchar2 ) return varchar2 is
len number(1) := length(in_char);
c_num number := null ;
c1 number;
c1_char varchar2(50);
c2 number;
c2_char varchar2(50);
out_char varchar2(100);
begin
c_num := to_number(in_char);
if c_num > 0 and c_num < 1000 then
out_char := to_char(to_date(in_char, 'J'), 'JSP');
else
out_char := null;
end if;
return(out_char) ;
end;
function F_SUB_1000000 (in_char varchar2 ) return varchar2 is
len number(1) := length(in_char);
c_num number := null ;
c1 number;
c1_char varchar2(100);
c2 number;
c2_char varchar2(100);
out_char varchar2(200);
begin
c_num := to_number(in_char);
if ( c_num >= 1000 and c_num < 1000000 ) then
c1 := FLOOR(c_num/1000);
c1_char := to_char(to_date(c1, 'J'), 'JSP');
c2 := c_num - (c1 * 1000);
if c2 > 0 then
c2_char := f_sub_1000(to_char(c2));
else
c2_char := null;
end if;
out_char := c1_char||' THOUSAND '||c2_char ;
else
out_char := f_sub_1000(in_char) ;
end if;
return(out_char) ;
end;
function F_SUB_1000000000 (in_char varchar2 ) return varchar2 is
len number(2) := length(in_char);
c_num number := null ;
c1 number;
c1_char varchar2(100);
c2 number;
c2_char varchar2(100);
out_char varchar2(200);
begin
c_num := to_number(in_char);
if ( c_num >= 1000000 and c_num < 1000000000 ) then
c1 := FLOOR(c_num/1000000);
c1_char := to_char(to_date(c1, 'J'), 'JSP');
c2 := c_num - (c1 * 1000000);
if c2 > 0 then
c2_char := f_sub_1000000 (to_char(c2));
else
c2_char := null;
end if;
out_char := c1_char||' MILLION '||c2_char ;
else
out_char := f_sub_1000000 (in_char);
end if;
return(out_char) ;
end;
function F_SUB_1000000000000 (in_char varchar2 ) return varchar2 is
len number(2) := length(in_char);
c_num number(18) := null ;
c1 number;
c1_char varchar2(100);
c2 number;
c2_char varchar2(100);
out_char varchar2(200);
begin
c_num := to_number(in_char);
if ( c_num >= 1000000000 and c_num < 1000000000000 ) then
c1 := FLOOR(c_num/1000000000);
c1_char := to_char(to_date(c1, 'J'), 'JSP');
c2 := c_num - (c1 * 1000000000);
if c2 > 0 then
c2_char := f_sub_1000000000 (to_char(c2));
else
c2_char := null;
end if;
out_char := c1_char||' BILLION '||c2_char ;
else
out_char := f_sub_1000000000 (in_char);
end if;
return(out_char) ;
end;
BEGIN
if to_number(in_char) = 0 then
return ('ZERO');
end if;
amt_with_cents := to_number(in_char);
full_amt := to_char(amt_with_cents, '9999999999990.90');
if to_number(substr(full_amt, 1, instr(full_amt, '.')-1)) <> 0 then
dollars := to_char(to_number(substr(full_amt, 1, instr(full_amt, '.')-1)));
else
dollars := '0';
end if;
if to_number(substr(full_amt, instr(full_amt, '.')+1)) <> 0 then
-- cents := ' AND PAISA '||to_char(to_date(substr(full_amt, -2, 2), 'J'), 'JSP');
cents := 'SAUDI RIYALS AND '||to_char(to_date(substr(full_amt, -2, 2), 'J'), 'JSP')||' HALALA(s) ONLY ';
else
cents := null;
end if;
len := length(dollars);
c_num := to_number(dollars);
if dollars = '0' then
return('ZERO '||cents);
elsif c_num < 1000 and cents is null then
return(f_sub_1000(dollars)||' SAUDI RIYALS ONLY'||cents);
elsif c_num < 1000 then
return(f_sub_1000(dollars)||' '||cents);
elsif c_num >= 1000 and c_num < 1000000 and cents is null then
return(f_sub_1000000(dollars)||' SAUDI RIYALS ONLY'||cents);
elsif c_num >= 1000 and c_num < 1000000 then
return(f_sub_1000000(dollars)||' '||cents);
elsif c_num >= 1000000 and c_num < 1000000000 and cents is null then
return(f_sub_1000000000(dollars)||' SAUDI RIYALS ONLY'||cents);
elsif c_num >= 1000000 and c_num < 1000000000 then
return(f_sub_1000000000(dollars)||' '||cents);
elsif c_num >= 1000000000 and c_num < 1000000000000 and cents is null then
return(f_sub_1000000000000(dollars)||' SAUDI RIYALS ONLY'||cents);
elsif c_num >= 1000000000 and c_num < 1000000000000 then
return(f_sub_1000000000000(dollars)||' '||cents);
else
return('the number is too large !');
end if;
END;
/
