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 having limitations 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 to 999999999999.
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;
/
Your feedback/comment is quite important for improving this blog so feel free to leave your comments & suggestions
