One is All You Need

As a infrequent poster (but constant lurker) on the Oracle forums, I often see questions about converting numbers to their English language equivalent. Recently, the PL/SQL and SQL forum was asked the opposite question: How does one convert words (i.e. “ONE HUNDRED TWENTY THREE”) to a number (i.e. 123). After the talented members of the forum came up with several solutions, I decided the whole topic was deserving of a SingleQuery posting.

Converting Numbers to Words

First the easy part. This is an old-school Oracle trick that’s been around forever:

SELECT TO_CHAR(TO_DATE(123,'J'),'JSP') to_words
FROM   dual;

TO_WORDS
------------------------
ONE HUNDRED TWENTY-THREE

Pretty straight forward. The TO_DATE(123,’J') creates the 123rd Julian Day, which is defined as the 123rd day after Monday, January 1, 4713 BC. Click here to learn more about the Julian Day if you feel obligated. The TO_CHAR with the ‘JSP’ format mask converts the Julian Day into the words “ONE HUNDRED TWENTY-THREE”.

Unfortunately, there are some limitations with this little trick. Oracle cannot compute the Julian Day past 31-DEC-9999 so that means we can only convert numbers to English up to 5,373,484 (the number of days between the date the pyramids were constructed and the date that the Chicago Cubs will win the World Series again). Also, this method only works in English; I have seen no solution to do this in other languages.

Converting Words to Numbers

Now the hard part. As previously mentioned, one of the Oracle forums recently discussed how one can convert from the English language to numbers. There were lots of interesting alternatives (click here to see the forum thread) with many focused upon procedural solutions. But since my specialty is a Single Query solution, I wanted to take a stab at it and came up with the following that works on 9i and 10g:

SELECT LEVEL wordasint
FROM   dual
WHERE  TO_CHAR(TO_DATE(LEVEL,'J'), 'JSP') = 'ONE HUNDRED TWENTY-THREE'
CONNECT BY TO_CHAR(TO_DATE(LEVEL-1,'J'),'JSP') != 'ONE HUNDRED TWENTY-THREE'
AND    LEVEL < 10001;

 WORDASINT
----------
       123

This is a rather strange CONNECT BY construct that you may not have seen before and a good explanation of what this does can be found here. It works, although with the similar maximum limitation of 5,373,484 (”FIVE MILLION THREE HUNDRED SEVENTY-THREE THOUSAND FOUR HUNDRED EIGHTY-FOUR”) because of the Julian Day. Also, the larger the number requested, the slower this query gets since it performs more and more CONNECT BYs as the requested number gets bigger. The last clause “AND LEVEL < 10001" is designed to stop typos (i.e. "ONES HUNDERD") from causing this query to execute until it exceeds the maximum. Simply provide the largest number+1 you will ever expect to receive and this query will return no rows if it is not found (but will not fail)!

Here is another slick solution for Oracle 10g (provided by Sundar M) that uses the MODEL clause, but it unfortunately stops functioning at 86399:

SELECT sp,
       n
FROM   (
        SELECT 'EIGHTY-SIX THOUSAND THREE HUNDRED NINETY-NINE' sp
        FROM dual
       )
   MODEL
   DIMENSION BY (1 dim)
   MEASURES (0 n, sp)
   RULES ITERATE (86400) UNTIL (TO_CHAR(DATE '0001-01-01' +
         (ITERATION_NUMBER/86400),'SSSSSSP')=sp[1])
         ( n[1]=ITERATION_NUMBER );

SP                                                     N
--------------------------------------------- ----------
EIGHTY-SIX THOUSAND THREE HUNDRED NINETY-NINE      86399

At 86400:

SP                                        N
-------------------------------- ----------
EIGHTY-SIX THOUSAND FOUR HUNDRED      86399  <--  Whoops!

Words to Numbers for Oracle 8/8i

The prior queries include functionality not available for Oracle 8/8i databases, but this one should work. I will admit, I don’t have an 8i database laying around, so this is untested:

SELECT theword,
       thenum
FROM
       (
        SELECT 'ZERO' theword,
               0      thenum
               FROM   dual
        UNION  ALL
        SELECT /*+ parallel (rbt 2) */
               TO_CHAR(TO_DATE(ROWNUM,'J'), 'JSP')  theword,
               ROWNUM
        FROM   really_big_table rbt
        WHERE ROWNUM <100001
       )
WHERE  theword  = 'TEN THOUSAND FIVE HUNDRED THIRTY-FOUR';

The limitations for this query are typical; you can’t generate numbers larger than 5,373,484 and you need a really_big_table to use as the seed table for generating rows (as a replacement for the CONNECT BY). Also, the bigger the maximum number (in this case 100,000), the longer this query will take to execute. However, this query can be modified for speed if specific numeric ranges are known in advance.

Dollars and Cents From Words

Oracle 8/8i (or 9i and 10g as well of course)i:

SELECT /*+ ordered all_rows */
       TO_CHAR(temp1.thenum+(temp2.thenum/100),'$9,999,999.99') amount
FROM
       (
        SELECT thenum,
               SUBSTR('ONE HUNDRED TWENTY-THREE AND TWELVE CENTS',
                 INSTR('ONE HUNDRED TWENTY-THREE AND TWELVE CENTS',' AND')+5,
                 INSTR('ONE HUNDRED TWENTY-THREE AND TWELVE CENTS',' CENTS')
                 - INSTR
                 ('ONE HUNDRED TWENTY-THREE AND TWELVE CENTS',' AND')
                 -5) thecents
        FROM
               (
                SELECT 'ZERO' theword,
                0      thenum
                FROM   dual
                UNION ALL
                SELECT TO_CHAR(TO_DATE(ROWNUM,'J'), 'JSP')  theword,
                       ROWNUM thenum
                FROM   really_big_table
                WHERE  ROWNUM < 100000
               )
        WHERE  theword=SUBSTR('ONE HUNDRED TWENTY-THREE AND TWELVE CENTS',1,
                   INSTR('ONE HUNDRED TWENTY-THREE AND TWELVE CENTS',' AND')-1)
       ) temp1,
       (
        SELECT 'ZERO' theword,
               0 thenum
        FROM DUAL
        UNION ALL
        SELECT TO_CHAR(TO_DATE(ROWNUM,'J'), 'JSP'),
               ROWNUM
        FROM   really_big_table
        WHERE  ROWNUM <101
       ) temp2
WHERE  temp1.thecents = temp2.theword;

AMOUNT
--------------
       $123.12

Oracle 9i/10g (formatted to fit in this page):

SELECT /*+ ordered all_rows */
       TO_CHAR(temp1.thenum+(temp2.thenum/100),'$9,999,999.99') amount
FROM
       (
        SELECT LEVEL thenum,
               SUBSTR('ONE HUNDRED TWENTY-THREE AND TWELVE CENTS',
                 INSTR('ONE HUNDRED TWENTY-THREE AND TWELVE CENTS',' AND')+5,
                 INSTR('ONE HUNDRED TWENTY-THREE AND TWELVE CENTS',' CENTS')
                 - INSTR
                 ('ONE HUNDRED TWENTY-THREE AND TWELVE CENTS','
                 AND')-5) thecents
        FROM    dual
        WHERE  TO_CHAR(TO_DATE(LEVEL,'J'), 'JSP') =
                 SUBSTR('ONE HUNDRED TWENTY-THREE AND TWELVE CENTS',1,
                 INSTR('ONE HUNDRED TWENTY-THREE AND TWELVE CENTS',' AND')-1)
        CONNECT BY TO_CHAR(TO_DATE(LEVEL-1,'J'),'JSP') !=
                     SUBSTR('ONE HUNDRED TWENTY-THREE AND TWELVE CENTS',1,
                     INSTR
                     ('ONE HUNDRED TWENTY-THREE AND TWELVE CENTS','
                     AND')-1)
        AND LEVEL < 100001
       ) temp1,
       (
        SELECT LEVEL thenum,
               TO_CHAR(TO_DATE(LEVEL,'J'), 'JSP') theword
        FROM   dual
        CONNECT BY 1 = 1
        AND LEVEL < 101
       ) temp2
WHERE  temp1.thecents = temp2.theword;

AMOUNT
--------------
       $123.12

Complete Solutions

Probably the most complete solution (but not a Single Query) was provided by a forum frequent poster that goes by the name of Volder. His combination of a query and a supporting function are only viable in Oracle 10g, but allow word descriptions of numbers up to 65 digits to be very quickly converted! For the sake of space, I am not reprinting his solution. Please refer to the forum thread for details.

If you have any other bright ideas on how to turn words into numbers, please don’t hesitate to post a comment. Happy coding!

Greg Pike