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
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 :)
Java URL Encoder/Decoder Example - In this tutorial we will see how to URL encode/decode…
Show Multiple Examples in OpenAPI - OpenAPI (aka Swagger) Specifications has become a defecto standard…
Local WordPress using Docker - Running a local WordPress development environment is crucial for testing…
1. JWT Token Overview JSON Web Token (JWT) is an open standard defines a compact…
GraphQL Subscription provides a great way of building real-time API. In this tutorial we will…
1. Overview Spring Boot Webflux DynamoDB Integration tests - In this tutorial we will see…
View Comments
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';
[code gutter="false" language="sql"]
SELECT TO_CHAR (TO_DATE (19, 'j'), 'jsp') FROM DUAL;
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
[/code]
The code needs to altered as below to fix the issue:
[code gutter="false" language="sql"]
SELECT TO_CHAR (TO_DATE (19, 'j','nls_calendar = ''gregorian'''), 'jsp','nls_calendar=''gregorian''') FROM DUAL
[/code]
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.
[code language="sql"]
SELECT
A.LCY_AMOUNT,
upper(case when length(floor(A.LCY_AMOUNT))>12 then TO_CHAR (TO_DATE (floor(floor(A.LCY_AMOUNT)/1000000000000), 'j'), 'jsp')
||' TRILLION '||DECODE(substr(floor(A.LCY_AMOUNT),length(floor(A.LCY_AMOUNT))-11,3), 0, '',(TO_CHAR(TO_DATE(to_number(substr(floor(A.LCY_AMOUNT),length(floor(A.LCY_AMOUNT))-11,3)),'J'),'JSP'))||' BILLION ')
||DECODE(substr(floor(A.LCY_AMOUNT),length(floor(A.LCY_AMOUNT))-8,3), 0, '',(TO_CHAR(TO_DATE(to_number(substr(floor(A.LCY_AMOUNT),length(floor(A.LCY_AMOUNT))-8,3)),'J'),'JSP'))||' MILLION ')
||DECODE(substr(floor(A.LCY_AMOUNT),length(floor(A.LCY_AMOUNT))-5), 0, '',(TO_CHAR(TO_DATE(substr(floor(A.LCY_AMOUNT),length(floor(A.LCY_AMOUNT))-5),'J'),'JSP')))
||decode((A.LCY_AMOUNT-floor(A.LCY_AMOUNT))*100,0,'',' AND '||TO_CHAR (TO_DATE ((A.LCY_AMOUNT-floor(A.LCY_AMOUNT))*100, 'j'), 'jsp')||' CENTS')
when length(floor(A.LCY_AMOUNT))>9 then TO_CHAR (TO_DATE (floor(floor(A.LCY_AMOUNT)/1000000000), 'j'), 'jsp')
||' BILLION '||DECODE(substr(floor(A.LCY_AMOUNT),length(floor(A.LCY_AMOUNT))-8,3), 0, '',(TO_CHAR(TO_DATE(to_number(substr(floor(A.LCY_AMOUNT),length(floor(A.LCY_AMOUNT))-8,3)),'J'),'JSP'))||' MILLION ')
||DECODE(substr(floor(A.LCY_AMOUNT),length(floor(A.LCY_AMOUNT))-5), 0, '',(TO_CHAR(TO_DATE(substr(floor(A.LCY_AMOUNT),length(floor(A.LCY_AMOUNT))-5),'J'),'JSP')))
||decode((A.LCY_AMOUNT-floor(A.LCY_AMOUNT))*100,0,'',' AND '||TO_CHAR (TO_DATE ((A.LCY_AMOUNT-floor(A.LCY_AMOUNT))*100, 'j'), 'jsp')||' CENTS')
when length(floor(A.LCY_AMOUNT))>=7 then TO_CHAR (TO_DATE (floor(floor(A.LCY_AMOUNT)/1000000), 'j'), 'jsp')
||' MILLION '||DECODE(substr(floor(A.LCY_AMOUNT),length(floor(A.LCY_AMOUNT))-5), 0, '',(TO_CHAR(TO_DATE(substr(floor(A.LCY_AMOUNT),length(floor(A.LCY_AMOUNT))-5),'J'),'JSP')))
||decode((A.LCY_AMOUNT-floor(A.LCY_AMOUNT))*100,0,'',' AND '||TO_CHAR (TO_DATE ((A.LCY_AMOUNT-floor(A.LCY_AMOUNT))*100, 'j'), 'jsp')||' CENTS')
else decode( (floor(A.LCY_AMOUNT)),0,'', ((TO_CHAR(TO_DATE((floor(A.LCY_AMOUNT)),'J'),'JSP'))))
||decode((A.LCY_AMOUNT-floor(A.LCY_AMOUNT))*100,0,'',' AND '||TO_CHAR (TO_DATE ((A.LCY_AMOUNT-floor(A.LCY_AMOUNT))*100, 'j'), 'jsp')||' CENTS')
end ) Amount
FROM ACVWS_ALL_AC_ENTRIES a
[/code]
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 ...