Looking for a function to convert numbers to words

Greetings, experts!

For a check-writing application, I need to convert numbers to words using a DB2 SQL function.

As you would expect, '23456' should translate to 'Twenty three thousand four hundred fifty six'.

Thinking that would be relatively straight-forward, I first tried to write it myself, but I quickly found that it wasnt as easy as I had anticipated.

So, then I tried searching Google for it  "Surely, SOMEONE has had to do this before", I thought. Unfortunately, all I could find were MS SQL Server versions that didn't easily translate to DB2 syntax.  I'm using DB2 for i ... version 6.1  ... a.k.a. V6R1.

So, before I go back to trying to write this myself, does anyone have this already written? Could you please supply this middle-aged DB2 guy the logic?

Thanks VERY much!
LVL 18
Dave FordSoftware Developer / Database AdministratorAsked:
Who is Participating?
Gary PattersonVP Technology / Senior Consultant Commented:

That'll teach you to tag a DB2/400 question outside of AS/400 zones!

IF you want to code it yourself, you can certainly pretty easily covert all of that VB to something useful on the AS/400, but if you are as lazy as I am, you'd prefer to find a more DB2/400-friendly solution: DB2/400 P-SQL, AS/400 CL, C, COBOL, RPG, or even Java.  Of course the ILE languages are going to give you the best repeated call performance.

Here is a nice one conveniently written in in ILE RPG.  Compile it and create an external stored procedure or user-defined function for use in DB2/400 SQL.


- Gary

Take a look at this:
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

does this version supports recursive sql?
Dave FordSoftware Developer / Database AdministratorAuthor Commented:

Wow! Thanks to all of you!

Let me try out these solutions, and I'll get back to you later today.

-- DaveSlash
Gary PattersonVP Technology / Senior Consultant Commented:

That is some interesting SQL, but with all due respect, the RPG solution will outperform your SQL solution by a very large margin (I do performance consulting for a living,and I specialize in the AS/400).  

Your solution forces the DBMS to open two tables, perform multiple joins, perform numerous random I/O operations, and perform at least one sort!  And that is repeated for every single conversion operation!  Now, imaging calling this thing a hundred thousand times to print a large check run!


Code them both and call them each a 100K times.  I'd love to see the runtime difference.

If you want a "pure SQL" solution, then convert the RPG algorithm to SQL procedure language.  Since DB2/400 generates a C program for SQL Procedures written in SQL Procedure Language, you'll get great performance.

- Gary
Dave FordSoftware Developer / Database AdministratorAuthor Commented:

Momi, that's really cool and clever. I wrapped the query in a CTE and selected only rows where the first four characteras are not 'ZERO'. That way it works correctly for smaller numbers.

Gary, thanks for the RPG service program. I compiled it as you specified, and I defined it as an external function in SQL. But, it doesn't seem to find it. I realze I'm probably missing something fundamental, but can you see what I'm missing? (see below)


CrtRPGMod  Module( MyLib/NbrToWords )
           SrcFile( MyLib/qrpglesrc )

Module NBRTOWORDS placed in library MYLIB. 00 highest severity. Created on 12/17/09 at 11:30:47.

CrtSrvPgm  SrvPgm( MyLib/NbrToWords )
           Export( *All )
           ActGrp( *Caller )

Service program NBRTOWORDS created in library MYLIB.

wrkobj *all/nbrtowords

Opt  Object      Type      Library     Attribute   Text                   
     NBRTOWORDS  *SRVPGM   MYLIB       RPGLE                              
     NBRTOWORDS  *MODULE   MYLIB       RPGLE       Covert Numbers To Words

CREATE FUNCTION MyLib/CvtNbrToWords (          
  v_number  integer
RETURNS varchar(200)

Function CVTNBRTOWORDS was created in MYLIB

select CvtNbrToWords(123456)
from   sysibm/sysdummy1     

NBRTOWORDS in MYLIB type *SRVPGM not found.

Open in new window

Gary PattersonVP Technology / Senior Consultant Commented:
Hmm.  Looks OK from here.  Couple of thoughts:

Try changing the procname to uppercase in the EXTERNAL NAME clause.  IF you check the service probram to see how the name is exported, it is probably mapped to upper case.
Does the profile that you are running the function under have rights to the service program?

- Gary

The RPG returns 200A. Is that going to map correctly to [RETURNS varchar(200)]? (It might. I haven't tried.)

Dave FordSoftware Developer / Database AdministratorAuthor Commented:

Thanks very much.

I love Momi's SQL solution.

I haven't had the time to get the RPG solution to work, but I'm sure it will. (It's probably something trivial that's causing it to not work.)

Thanks again!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.