Convert number to words 1

[kajarekar]

Has anybody developed a user defined function for converting numbers to words (which is especially useful for printing of invoices and cheques). This function should accept numeric value as input and return the word translation. e.g. if input is 150.25, it should return "One Hundred Fifty and Twenty Five".

The supportlink magzine of Cognos for July-September 2000 gives a function which runs on Oracle. But it will not run on SQL-2000 directly unless some changes are made.

I am a business analyst using Impromptu. I know little on the programming side (i.e. writing codes, stored procedures and UDF's). Can anybody help me on this ?

Thanks

 

[Shiseiji]

Did you get an answer? I may have a link to a VB routine written for Excel you should be avle to convert.

 

[kajarekar]

Hi Shiseiji

I already got the answers. But thanks for your help. somebody from the SQL Server Forum of Tek-tips helped me out. The function is working absolutely fine in Cognos Impromptu.

 

[griffindm]

kajarekar,

Can you share the SQL Server code for the function here so others might use it?

Thanks,

Dave Griffin

 

[kajarekar]

Hi Griffin,

Why not. My pleasure. Here it goes.

*********************************************
I have used the following command to create a table in SQL (this table will be used as a lookup table for converting numbers to text)

************************************************************
CREATE TABLE THOUSAND (
num int,
wrd varchar(30)
)

INSERT INTO THOUSAND VALUES (0,'')
INSERT INTO THOUSAND VALUES (1,'One')
INSERT INTO THOUSAND VALUES (2,'Two')
INSERT INTO THOUSAND VALUES (3,'Three')
INSERT INTO THOUSAND VALUES (4,'Four')
INSERT INTO THOUSAND VALUES (5,'Five')
INSERT INTO THOUSAND VALUES (6,'Six')
INSERT INTO THOUSAND VALUES (7,'Seven')
INSERT INTO THOUSAND VALUES (8,'Eight')
INSERT INTO THOUSAND VALUES (9,'Nine')
INSERT INTO THOUSAND VALUES (10,'Ten')
INSERT INTO THOUSAND VALUES (11,'Eleven')
INSERT INTO THOUSAND VALUES (12,'Twelve')
INSERT INTO THOUSAND VALUES (13,'Thirteen')
INSERT INTO THOUSAND VALUES (14,'Fourteen')
INSERT INTO THOUSAND VALUES (15,'Fifteen')
INSERT INTO THOUSAND VALUES (16,'Sixteen')
INSERT INTO THOUSAND VALUES (17,'Seventeen')
INSERT INTO THOUSAND VALUES (18,'Eighteen')
INSERT INTO THOUSAND VALUES (19,'Nineteen')
INSERT INTO THOUSAND VALUES (20,'Twenty')
INSERT INTO THOUSAND VALUES (30,'Thirty')
INSERT INTO THOUSAND VALUES (40,'Forty')
INSERT INTO THOUSAND VALUES (50,'Fifty')
INSERT INTO THOUSAND VALUES (60,'Sixty')
INSERT INTO THOUSAND VALUES (70,'Seventy')
INSERT INTO THOUSAND VALUES (80,'Eighty')
INSERT INTO THOUSAND VALUES (90,'Ninety')

INSERT INTO THOUSAND
SELECT A.num+B.num, A.wrd+'-'+B.wrd
FROM (SELECT * FROM THOUSAND WHERE num BETWEEN 20 AND 90) A
CROSS JOIN (SELECT * FROM THOUSAND WHERE num BETWEEN 1 AND 9) B

INSERT INTO THOUSAND
SELECT A.num*100+B.num, RTRIM(A.wrd+' '+'hundred '+B.wrd)
FROM (SELECT * FROM THOUSAND WHERE num BETWEEN 1 AND 9) A
CROSS JOIN THOUSAND B
************************************************************
After this, a user defined function in SQL was created, the script of which is as follows :

************************************************************



CREATE FUNCTION NameNum (
@n Numeric(15,0)
) returns varchar(400) as begin
if @n = 0
return 'Zero'
declare @s varchar(15)
set @s = right(replicate('0',15)+cast(@n as varchar(15)),15)
declare @w varchar(400)
set @w = ''
if left(@s,3) > 0
set @w = @w + (select wrd from THOUSAND where num=left(@s,3))
+ ' Trillion '
if left(right(@s,12),3) > 0
set @w = @w + (select wrd from THOUSAND where num=left(right(@s,12),3))
+ ' Billion '
if left(right(@s,9),3) > 0
set @w = @w + (select wrd from THOUSAND where num=left(right(@s,9),3))
+ ' Million '
if left(right(@s,6),3) > 0
set @w = @w + (select wrd from THOUSAND where num=left(right(@s,6),3))
+ ' Thousand '
if right(@s,3) > 0
set @w = @w + (select wrd from THOUSAND where num=right(@s,3))
return rtrim(@w)
end

************************************************************Once this function is created, you can test it in SQL and then u r ready to tag it into Impromptu

I have added following entries in different files of Impromptu


msfunct.ini (ms because I am using MS SQL Server as a database)
------------------------------------------------------------[Database-specific Function List]
SUN426.dbo.NameNum=

note: here sun426 is the name of database in which UDF called NameNum was created
Add following entry also to the same file in the mentioned section

; Database-specific Function Details

[SUN426.dbo.NameNum]
label=NameNum
param=1
return=CH
1=NM;numeric_exp
exp=NameNum ( ^1 )
tip=Syntax: Under Test
tip1=Numeric expression

cogudfms.sql
--------------------
DECLARE DATABASE FUNCTION NameNum( NUMBER )
RETURNS STRING
FUNCTION NAME "SUN426.dbo.NameNum";

************************************************************
Once this is done, you should be able to see the function in the impormptu query dialogue box

Important : this function does not work if you are connecting to MS SQL in native mode. To overcome, connect via ODBC