How can you convert a number into words using Oracle Sql Query? What I mean by Number to Word is:
Code language: SQL (Structured Query Language) (sql)12 = Twelve 102 = One Hundred Two 1020 = One Thousand Twenty
Here’s a classy query which will convert number into words.Please see the query below:
select to_char(to_date(:number,'j'),'jsp') from dual;Code language: SQL (Structured Query Language) (sql)If I pass 234 in number, then the output will : two hundred thirty-four
SELECT TO_CHAR (TO_DATE (234, 'j'), 'jsp') FROM DUAL;
//Output: two hundred thirty-four
SELECT TO_CHAR (TO_DATE (24834, 'j'), 'jsp') FROM DUAL;
//Output: twenty-four thousand eight hundred thirty-four
SELECT TO_CHAR (TO_DATE (2447834, 'j'), 'jsp') FROM DUAL;
//Output: two million four hundred forty-seven thousand eight hundred thirty-four
Code language: SQL (Structured Query Language) (sql)So how the query works? Well here’s why: 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:
Code language: SQL (Structured Query Language) (sql)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 (53734555555585) FROM DUAL;
Code language: SQL (Structured Query Language) (sql)Output:
Fifty-Three trillion Seven Hundred Thirty-Four billion Five Hundred Fifty-Five million Five Hundred Fifty-Five thousand Five Hundred Eighty-Five
Hope this helps :)
When i am running this query i see the below error
[SQL] SELECT TO_CHAR (TO_DATE (24834, ‘j’), ‘jsp’) FROM DUAL;
[Err] 1305 – TO_CHAR does not exist
Hi Atul, Check the output for
SELECT TO_DATE (24834, ‘j’) FROM DUAL. Can you tell me what output you getting for this query?how to convert number into words using oracle forms
not using date function like 23=twenty three
SELECT TO_CHAR (TO_DATE (24834, ‘j’), ‘jsp’) FROM DUAL
Dear Atul, copy this one and try..u will get result.
Yes thankyou..this one is working…………
#1305 – FUNCTION TO_DATE does not exist
As he mentioned before the same error are reflecting.. there is no solution for this.?
Nice and Handy technique.. Found a good thing on your rss feed after quite some time.
I Guess a very handful of people knew about it.
Is it Oracle only trick or is it valid for any SQL (like MySQL or SQLite) ?
Hi, The functions that we used here
to_char&to_dateare oracle only. I am sure there might be similar methods with other DBs.the max it can fetch is 1 million. is there any way to get more. just curious..
Srini, That’s the limit of Julian date. I have updated the post and added a workaround for that problem. Have a look.
Hi..
I tried to deduce a MySQL version for this trick. There is a STR_TO_DATE and DATE_FORMAT as to_date() but couldn’t find a to_char() equivalent.
I tried several manipulation from http://www.ispirer.com/doc/sqlways/Output/SQLWays-1-073.html
but either i get a NULL or a error.
Can you help me to get this trick done on a MySQL DB.
Hi,
One other limitation that needs to be considered is that this code won’t work if the NLS_CALENDAR is not set to Gregorian:
alter session set nls_calendar = ‘arabic hijrah’;
The code needs to altered as below to fix the issue:
That’s Great buddy,
but how we can utilitse this function on the oracle 6i forms to show the result on Dispaly Item.
?????????????
I tried but not worked.
how to get output if we have digit like 223.32 (means in decimal)
select to_char(to_date(floor(1234.99),’J’),’Jsp’)||’ and ‘||
to_char(to_date((1234.99-(floor(1234.99)))*100,’J’),’Jsp’)|| ‘ paise’ from dual
If any one wants a single sql query to get this done please follow the below. It will show you till trillion amount but you can modify the same.
A decimal number to word conversion.
Can any one help me to get the results in indian format…like Crore and Lack and Thousands and Tens
Hi friends kindly help me to
How to convert in to lakhs and cores ??
i dont need in million & billion …
Hi.
I want to convert a given date in words e,g 14/01/1912 to Fourteen January Nineteen hundred and twelve.
can any one help me
Hi.
I want to convert a given date in words e,g 14/01/1912 to Fourteen January Nineteen hundred and twelve using oracle
can any one help me
SQL> SELECT TO_CHAR(TO_DATE(’14/01/1912′,’DD/MM/YYYY’),’DDSP FMMONTH YYYYSP’) Year FROM DUAL;
YEAR
————————————————-
FOURTEEN JANUARY ONE THOUSAND NINE HUNDRED TWELVE
SQL> SELECT TO_CHAR(TO_DATE(’14/01/1912′,’DD/MM/YYYY’),’DDSP FMMONTH YEAR’) Year FROM DUAL;
YEAR
——————————–
FOURTEEN JANUARY NINETEEN TWELVE
Query For use fractional part as string like 1.10 will display one point ten ????
i have one query , when i execute the query it gives the this error
ORA-01830: date format picture ends before converting entire input string
the query
SELECT NI.POLICY_HOLDER_SALUTATION || ‘ ‘ ||PD.DRAWER_NAME AS DRAWER_NAME,
PD.ADDRESS_LINE_1 || NVL2(PD.ADDRESS_LINE_1,’,’,”) || PD.ADDRESS_LINE_2 || NVL2(PD.ADDRESS_LINE_2,’,’,”) || PD.ADDRESS_LINE_3 || NVL2(PD.ADDRESS_LINE_3,’,’,”) || PD.CITY || NVL2(PD.CITY,’,’,”) || PD.PINCODE || NVL2(PD.PINCODE,’,’,”) || CHR(10) || PD.STATE AS DRAWER_ADDRESS,
PD.DRAWER_CONTACT_NO AS DRAWER_CONTACT_NO,
TO_CHAR(PD.LOGIN_DATE,’DD-Mon-YYYY’) AS LOGIN_DATE ,
NVL((CASE WHEN PAYMENT_MODE IN (‘Cheque’,’Demand Draft’) THEN TO_CHAR(PD.INSTRUMENT_DATE,’DD-Mon-YYYY’)
WHEN PAYMENT_MODE IN (‘Cash’,’OTC Yes Bank’) THEN ”
ELSE TO_CHAR(PD.LOGIN_DATE,’DD-Mon-YYYY’) END),’ ‘) AS INSTRUMENT_DATE,
PD.USER_BRANCH_NAME,
PD.RECEIPT_NUMBER,
TO_CHAR(PD.AMOUNT,’999999999.99′) AS AMOUNT,
NVL(PD.POLICY_STATUS,’ ‘) POLICY_STATUS,
NVL((CASE WHEN PD.PAYMENT_MODE IN (‘Cash’,’OTC Yes Bank’) THEN NULL ELSE PD.INSTRUMENT_NUMBER END),’ ‘) AS INSTRUMENT_NUMBER,
NVL(PD.DRAWER_BANK,’ ‘) AS DRAWER_BANK,
PD.CONTRACT_NUMBER,
NVL(PD.EMAIL_ID,’ ‘) EMAIL_ID,
NVL(PD.PLAN_NAME,’ ‘) PLAN_NAME,
NVL(PD.PREMIUM_FREQUENCY,’ ‘) PREMIUM_FREQUENCY,
TO_CHAR(PD.INSTRUMENT_DATE,’DD-Mon-YYYY’),
PD.CHEQUE_RECEIVE_DATE, TRIM(f_words(PD.AMOUNT)) AS AMOUNT_WORDS_OLD,
UPPER((TO_CHAR (TO_DATE (PD.AMOUNT, ‘j’), ‘jsp’) || ‘ ‘ || ‘only’)) AS AMOUNT_WORDS,
TO_NUMBER(PD.AMOUNT,’999999999.99′) AMOUNT1,TRIM(TO_CHAR(PD.AMOUNT,’999,999,999.99′)) AMOUNT2,NVL(TO_CHAR(PD.RISK_COMMENCE_DATE,’DD-Mon-YYYY’),’ ‘) AS RISK_COMMENCE_DATE,
PD.PAYMENT_MODE AS PAYMENT_METHOD
FROM PAYMENT_DETAILS PD,NEWBUSS.NB_INWARD@NB_CCS NI WHERE PD.CONTRACT_NUMBER=NI.APPLICATION_ID AND PD.RECEIPT_NUMBER=$P{RECEIPT_NUMBER}
i am using this query to convert for this number 1224834
its return for :
one million two hundred twenty-four thousand eight hundred thirty-four
but i want result like :
Twelve Lakhs Twenty Four Thousand Eight Hundred And Thirty Four Only
any ideas ?
CREATE OR REPLACE FUNCTION APPS.num_to_word (p_num2 IN NUMBER)
RETURN VARCHAR2
IS
p_num1 NUMBER;
p_round NUMBER;
p_num NUMBER;
p_scale NUMBER;
p_s_word VARCHAR2 (100);
v_num_word VARCHAR2 (2000);
l_num NUMBER;
v_word VARCHAR2 (2000);
v_word1 VARCHAR2 (2000);
v_word2 VARCHAR2 (2000);
v_word3 VARCHAR2 (2000);
BEGIN
p_num1 := ABS (p_num2);
p_round := ROUND (p_num1, 5);
SELECT SUBSTR (p_num1,
1,
DECODE (INSTR (p_num1, ‘.’) – 1,
-1, LENGTH (p_num1),
INSTR (p_num1, ‘.’) – 1
)
)
INTO p_num
FROM DUAL;
IF INSTR (p_num1, ‘.’) > 0
THEN
p_scale := SUBSTR (p_round, INSTR (p_round, ‘.’) + 1);
IF LENGTH(p_scale)=1 THEN
SELECT TO_CHAR (TO_DATE (p_scale||’0′, ‘j’), ‘jsp’)
INTO p_s_word
FROM DUAL;
ELSE
SELECT TO_CHAR (TO_DATE (p_scale, ‘j’), ‘jsp’)
INTO p_s_word
FROM DUAL;
end if;
END IF;
l_num := LENGTH (p_num);
IF l_num 5 AND l_num = 8
THEN
SELECT TO_CHAR (TO_DATE (SUBSTR (p_num, 1, l_num – 7), ‘j’), ‘jsp’)
INTO v_word1
FROM DUAL;
IF SUBSTR (p_num, l_num – 7 + 1, 2) > 0
THEN
SELECT TO_CHAR (TO_DATE (SUBSTR (p_num, l_num – 7 + 1, 2), ‘j’),
‘jsp’
)
INTO v_word2
FROM DUAL;
END IF;
SELECT TO_CHAR (TO_DATE (LTRIM (SUBSTR (p_num, l_num – 5 + 1), 0), ‘j’),
‘jsp’
)
INTO v_word3
FROM DUAL;
IF SUBSTR (p_num, l_num – 7 + 1, 2) > 0
THEN
v_word := v_word1 || ‘ crore ‘ || v_word2 || ‘ lakh ‘ || v_word3;
ELSE
v_word := v_word1 || ‘ crore ‘ || v_word3;
END IF;
END IF;
IF INSTR (p_num1, ‘.’) > 0
THEN
v_num_word :=
INITCAP (‘Rs. ‘ || v_word || ‘ and ‘ || p_s_word || ‘ paisa only.’
);
ELSE
v_num_word := INITCAP (‘Rs. ‘ || v_word || ‘ only.’);
END IF;
IF SIGN (p_num2) = -1
THEN
RETURN ‘-‘ || v_num_word;
ELSE
RETURN v_num_word;
END IF;
END;
/
please give any feedback regarding this query
You people don’t understand the beauty of the small code thats why I am seeing so many complex functions after such a nice and handy explanation.
convert number to arabic words in oracle
ex
12=
اثنا عشر
123 = مائة وثلاث وعشرون
Thanks
Thanks a lot
SELECT TO_CHAR(TO_date(199920,’J’), ‘JSP’)into words FROM dual;
How to display 199920 in words, now it is coming as one hundred ninty nine thousand nine hundred twenty instead i want one lakh ninety nine thousand nine hundred twenty
SELECT TO_CHAR(TO_date(199920,’J’), ‘JSP’)into words FROM dual;
How to display 199920 in words, now it is coming as one hundred ninty nine thousand nine hundred twenty instead i want one lakh ninety nine thousand nine hundred twenty
The second FUNC doesnt take care of decimal values, just modified a bit,
CREATE OR REPLACE FUNCTION AmtToWords (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;
l_return := l_return || ‘ Rupees’;
if to_char( p_number ) like ‘%.%’
then
l_num := rpad(substr( p_number, instr( p_number, ‘.’ )+1 ),2,0);
if l_num > 0
then
l_return := l_return || ‘ And ‘|| to_char(to_date( l_num,’J’ ),’Jsp’ ) || ‘ Paisa.’;
end if;
end if;
RETURN l_return;
END;
/
select amttowords(111818181.91) from dual
One Hundred Eleven million Eight Hundred Eighteen thousand One Hundred Eighty-One Rupess And Ninety-One Paisa.
Great trick – thanks! I found the most common gotcha was if you pass a 0 value into your first simple nested function, it crashes the statement. Best to wrap it in a CASE statement.
good article
Here is one an other option for converting numbers into rupees . just small function.
just create function and use in your oracle reports or oracle forms or database.
FUNCTION rupees(num_val IN NUMBER) RETURN VARCHAR2
IS
/* This functions is used to convert number into words upto trillion */
mil VARCHAR2(2000) := ‘ TRILLIONBILLION MILLION THOUSAND’;
mwords VARCHAR2(2000) := null;
mwords_d VARCHAR2(2000) := null;
ctr NUMBER := 0;
num3 NUMBER;
mnum NUMBER;
mdec NUMBER :=0;
val NUMBER;
BEGIN
val := round(abs(num_val),2);
mnum := trunc(val);
mdec := to_number(replace(to_char(val – trunc(mnum)),’.’));
LOOP
ctr := nvl(ctr,0) + 1;
EXIT WHEN ctr > 5;
num3 := TO_NUMBER(SUBSTR(LPAD(TO_CHAR(mnum),15,’0′),ctr*3-2,3));
IF num3 > 0 THEN
mwords := mwords||’ ‘||TO_CHAR(TO_DATE(num3,’J’),’JSP’);
mwords := mwords||’ ‘||RTRIM(SUBSTR(mil,ctr*8,8));
END IF;
END LOOP;
IF nvl(mdec,0) > 0 THEN
mwords_d := ‘AND ‘||TO_CHAR(TO_DATE(mdec,’J’),’JSP’);
elsif nvl(mdec,0) = 0 then
mwords_d := ‘Zero ‘ ;
End if;
RETURN initcap(mwords||’ Rupees ‘||mwords_d|| ‘ Paisas Only’);
END rupees;
if numer =0 how to spell the numer?and floating point,negative numbers
I KNOW ONLY FLOATING POINT NUMBERS,
SELECT
TO_CHAR(TO_DATE(FLOOR(234.56),’J’), ‘JSP’)
FROM DUAL;