How To Convert Number into Words using Oracle SQL Query






How can you convert a number into words using Oracle Sql Query? What I mean by Number to Word is:
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;

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
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:
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;
Output:
Fifty-Three trillion Seven Hundred Thirty-Four billion Five Hundred Fifty-Five million Five Hundred Fifty-Five thousand Five Hundred Eighty-Five

Comments

Post a Comment

Popular posts from this blog

Swift Learning Roadmap

Image
Recently one of my email subscribers wrote me this: What I find is that I’m learning the various steps but I do not understand the “big picture”. For me, a guideline, would be helpful. I know Swift is available but I do not even know object oriented programming. I’d really like to see a “roadmap” that says: if you know or do not know this, start here and learn this, then this, etc. He is not alone. Talking to people and reading comments online I found that many share the same feeling. There is a huge amount of information about iOS development and so many things to learn. It is easy to get lost and not know what is important to know and what should be learned first. I know that feeling myself. When I started adventuring into Mac OS X programming in 2005 (the iPhone did not exist yet) I found myself in the same situation. Back then, resources were scarcer than nowadays. I relied a lot on Apple’s documentation and started venturing into the world of Cocoa and...
Read more

How to Apply for an iOS Developer Job

This is the first part of a three part series on applying and interviewing for an iOS developer job. In this first part of the series, you will focus on the applying part of the equation – i.e. preparing your resume and cover letter. In this second part of the series, Ray will show you a few examples of some iOS resumes and cover letters he likes that might be useful for reference or models. In the third part of the series, you will focus on the interviewing part of the equation – i.e. what types of questions to expect and prepare for, and how to do your best in an interview. Note that none of these articles will focus on how to get the required iOS skills (although that might be a good follow-up article some day). Let’s assume you’re already a seasoned iOS developer. After all, if you’re reading this blog, that’s a good sign that honing your iOS skills is important to you! :] Also, none of these articles will focus on where to find iOS jobs (although that’s another...
Read more

Partitioning and Naming Drives (Windows 8)

Image
Understanding drives, partitions, and volumes A partition is an area of a hard disk that can be formatted and assigned a drive letter. On a hard disk, a volume is a formatted primary partition or logical drive. The terms partition and volume are often used interchangeably. HP and Compaq computers with Windows 8 have hard disk drives that contain at least two partitions: the main partition for Windows and your work, and a recovery partition to restore the computer back the way it came from the factory. Here is a list of some of the partitions you might find on your hard drive: The main partition, usually labeled with the letter C, contains the system files, program files, and usable file storage space. A recovery partition, usually drive letter D and labeled RECOVERY, contains system recovery information in case the files on the C partition are damaged or unusable. A hidden partition might exist called SYSTEM. A SYSTEM partition is used by Wind...
Read more