Thursday, December 17, 2009

Converting Number to Words Using SQL

Two posts in one week? Christmas came early this year… :-)

There is a web site called experts exchange where people ask questions and other people answer. I spend some time answering questions there and today someone asked for a solution for the following problem:
He has a number and he needs to convert it to text, for example, convert 10 to ten.
This is required for some check handling application.

The guy was referred to some code written in RPG, but I decided this can be a nice SQL challenge.

So, after a bit of fooling around, this is what I came up with:
First thing to do is to setup two tables that will contain some metadata. You don't have to actually create these tables; you can use a CTE if your version of DB2 supports it.

CREATE TABLE MOMI.FACTORS(START INT, LENGTH INT, CAPTION VARCHAR(20));
INSERT INTO MOMI.FACTORS VALUES(3, 3, '');
INSERT INTO MOMI.FACTORS VALUES(6, 3, 'THOUSAND');
INSERT INTO MOMI.FACTORS VALUES(9, 3, 'MILLION');
INSERT INTO MOMI.FACTORS VALUES(12, 3, 'BILLION');
INSERT INTO MOMI.FACTORS VALUES(15, 3, 'TRILLION');

CREATE TABLE MOMI.NUMS (VAL INT, CAPTION VARCHAR(20));
INSERT INTO MOMI.NUMS VALUES( 0, 'ZERO');
INSERT INTO MOMI.NUMS VALUES( 1, 'ONE');
INSERT INTO MOMI.NUMS VALUES( 2, 'TWO');
INSERT INTO MOMI.NUMS VALUES( 3, 'THREE');
INSERT INTO MOMI.NUMS VALUES( 4, 'FOUR');
INSERT INTO MOMI.NUMS VALUES( 5, 'FIVE');
INSERT INTO MOMI.NUMS VALUES( 6, 'SIX');
INSERT INTO MOMI.NUMS VALUES( 7, 'SEVEN');
INSERT INTO MOMI.NUMS VALUES( 8, 'EIGHT');
INSERT INTO MOMI.NUMS VALUES( 9, 'NINE');
INSERT INTO MOMI.NUMS VALUES( 10, 'TEN');
INSERT INTO MOMI.NUMS VALUES( 11, 'ELEVEN');
INSERT INTO MOMI.NUMS VALUES( 12, 'TWELVE');
INSERT INTO MOMI.NUMS VALUES( 13, 'THIRTEEN');
INSERT INTO MOMI.NUMS VALUES( 14, 'FOURTEEN');
INSERT INTO MOMI.NUMS VALUES( 15, 'FIFTEEN');
INSERT INTO MOMI.NUMS VALUES( 16, 'SIXTEEN');
INSERT INTO MOMI.NUMS VALUES( 17, 'SEVENTEEN');
INSERT INTO MOMI.NUMS VALUES( 18, 'EIGHTEEN');
INSERT INTO MOMI.NUMS VALUES( 19, 'NINETEEN');
INSERT INTO MOMI.NUMS VALUES( 20, 'TWENTY');
INSERT INTO MOMI.NUMS VALUES( 30, 'THIRTY');
INSERT INTO MOMI.NUMS VALUES( 40, 'FORTY');
INSERT INTO MOMI.NUMS VALUES( 50, 'FIFTY');
INSERT INTO MOMI.NUMS VALUES( 60, 'SIXTY');
INSERT INTO MOMI.NUMS VALUES( 70, 'SEVENTY');
INSERT INTO MOMI.NUMS VALUES( 80, 'EIGHTY');
INSERT INTO MOMI.NUMS VALUES( 90, 'NINETY');

And now for the query itself:

SELECT
CASE WHEN T3.VAL > 0 THEN ' '||T3.CAPTION||' HOUNDRED' ELSE '' END||
CASE WHEN T4.VAL IS NOT NULL THEN
CASE WHEN T3.VAL>0 AND T2.VAL=0 THEN ' AND '
WHEN COMPNAME = '' THEN ' AND '
ELSE ' ' END ||T4.CAPTION
WHEN T2.VAL = 0 THEN ' AND '||T1.CAPTION
WHEN T1.VAL > 0 AND T2.VAL >= 2 THEN ' '||T2.CAPTION||
CASE WHEN COMPNAME='' THEN ' AND '
ELSE ' ' END ||
T1.CAPTION END || ' '||T.COMPNAME
FROM (
SELECT
INT(LEFT(RIGHT(REPEAT('0',16-LENGTH(T1.TXT))||T1.TXT,
T2.START),T2.LENGTH)) COMPONENT, T2.START ,
T2.CAPTION COMPNAME
FROM (SELECT '17431103114512' AS TXT FROM SYSIBM.SYSDUMMY1) T1
, MOMI.FACTORS T2
) T
JOIN MOMI.NUMS T1 ON MOD(T.COMPONENT,10) = T1.VAL
JOIN MOMI.NUMS T2 ON MOD(T.COMPONENT/10, 10)*10 = T2.VAL
JOIN MOMI.NUMS T3 ON MOD(T.COMPONENT/100, 10) = T3.VAL
LEFT JOIN MOMI.NUMS T4 ON MOD(T.COMPONENT, 100) = T4.VAL
ORDER BY START DESC

Your first response should be what the @#!#@ ?
But after I'll explain, I hope you will feel differently.
What this query does is dividing the number into groups of 3 digits that represents the amount of each magnitude (ones, thousands, millions, trillions etc…).
Then, each group of 3 digits is translated to a string that represents it and then concatenated to the text that describes the magnitude.
This can obviously be scaled up to any number, but since this is an application that deals with money, I figured trillions should do the job.

The only problem with this query is that the result is not contained in a single line, but in multiple lines.
One can overcome this problem by creating a stored procedure that runs the above query and concatenate all the results. Another way to do it is use the above query in a CTE and join it to itself several times.

The advantages of having this done using a query over some other non-SQL language are:
1) It will work in every DB2 on every platform (I used pretty simple syntax, no special functions)
2) It will probably run faster
3) It is easier to maintain for a DBA
4) It can be executed against many values with no extra effort (just join to a table with the values you wish to convert)
Comments are welcomed as always.
Good Day

5 comments:

  1. Not a bad start for your blog. Keep up the good work.

    ReplyDelete
  2. I'm the guy that suggested the RPG solution. I agree that there are lots of benefits to an SQL solution to the EE question, but performance is definitely not one of them.

    Don't get me wrong, this is some interesting SQL, but with all due respect, it is going to be an absolute pig from a performance perspective.

    DB2 has to open two tables, perform multiple joins, numerous random I/O operations, and at least one sort for every single conversion operation.

    Write a table-free stored procedure - it will be MUCH faster.

    ReplyDelete
  3. Gary,

    I did not mean to say your solution wasn't good, and to be perfectly honest, I don't know as400 so I don't know how well DB2 integrates with RPG there, but I disagree with what you posted in your comment.
    Each one of my tables can fit in a single page, so there will be no numerous random I/O operations, not more than 2, and as I have mentioned, maybe using a CTE will make it faster.
    The sort operation will sort at most 5 records each time, so this should not be a problem as well.
    I just think that making a context switch to some RPG code will take more time.
    Obviously writing a table free SQL/PL stored procedure will be a better solution, but it will not allow someone to perform bulk translation on many values at once.

    ReplyDelete
  4. Hi Momi,
    Stumbled upon this blog entry while I was searching for some information. Your blogs are very thoughtful and informative. Thanks. Made an attempt to provide a solution to the numbers problem. This makes use of recursion(using CTE). I must admit that I got a fair bit of this idea by looking at your solution. Was not able to include my SQL here. Have sent you a mail.

    Cheers - Sriram (L.Sriram@gmail.com)

    ReplyDelete
  5. Hi
    You can find an alternative solution by Sriram here
    http://blog.badvised.com/2010/05/converting-number-to-words-using-sql.html

    ReplyDelete