[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Looking for a function to convert numbers to words

Posted on 2009-12-16
Medium Priority
Last Modified: 2012-05-08
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!
Question by:Dave Ford
  • 3
  • 3
  • 2
  • +2
LVL 39

Expert Comment

ID: 26068465
Take a look at this:
LVL 36

Accepted Solution

Gary Patterson earned 1000 total points
ID: 26068849

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 37

Expert Comment

ID: 26069480
does this version supports recursive sql?
LVL 37

Assisted Solution

momi_sabag earned 1000 total points
ID: 26070451
LVL 18

Author Comment

by:Dave Ford
ID: 26072832

Wow! Thanks to all of you!

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

-- DaveSlash
LVL 36

Expert Comment

by:Gary Patterson
ID: 26073745

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
LVL 18

Author Comment

by:Dave Ford
ID: 26074803

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

LVL 36

Expert Comment

by:Gary Patterson
ID: 26074903
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
LVL 27

Expert Comment

ID: 26077138

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

LVL 18

Author Closing Comment

by:Dave Ford
ID: 31667094

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!

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question