Wednesday, 3 February 2016

Convert number into words using Oracle SQL

Requirement: I have a number (20041986) and I want to display the number in word format as shown below:

TWO MILLION ONE THOUSAND NINE HUNDRED EIGHTY-SIX

Solution: For solution to the above requirement follow the steps as shown below:

Step 1: First we will convert the number (20041986) into DATE format.

Step 2: Then we will convert the converted date in step 1 to words.

Thus the complete query to do this is shown below:

SELECT TO_CHAR(TO_DATE(2001986,'J'),'JSP') IN_WORDS FROM DUAL;

In the above query:
1.      ‘J’ is used for converting a specific integer into Julian Date.
2.      ‘JSP’ stands for Julian Spelling with all words in upper case. We can use ‘jsp’ for all small case and ‘Jsp’ for initial capital case.

Hence the solution to our requirement.

If you like the post please comment, share and like me on Facebook.


Thanks & Regards,
Susanto Paul