Convert Numeric Figures into Words
Hi guys
What about a function that converts a number figure into words.This sample script is to demonstrate the procedural capabilities of SQL Server .
Samples select fig2words(10) will give --Ten-- ,select fig2words(103) will give --one hundred and three --
The author uses much under utilized capability of SQL Server the recursive function calls to implement this inherently recursive logic many will raise their eyebrows and will complain about performance issues, dont worry ,no problem at all my warranty is there.
This function uses a base table named units_table ,as described after the script ,pl create this table and populate it with the subsequent statement. Now you can go for the creation of the function, just replace the user name here "john" with your session user name.
Recursive logic is very clear and author dont want to put even his name as comment in order not to disturb the free flow of the logic
How about including this in standard convert function !!!
Have nice time
Regards John
See Rezvan modifications down the code
CREATE FUNCTION fig2words
(@FigInNum int)
returns varchar(255)
as
begin
declare @numUnit int
declare @numUnitKount int
declare @remainder int
declare @figInWords varchar(255)
set @numUnit=(select max(num_unit) from units_table where num_unit <= @FigInNum )
if @FigInNum <=0
begin
set @figInWords=''
return(@figInWords)
end
if @numUnit= @FigInNum
begin
set @figInWords=(select InWords from units_table where num_unit = @FigInNum )
return (@figInWords)
end
if @numUnit >=100
begin
set @numUnitKount=@FigInNum/@numUnit
set @remainder=@FigInNum%@numUnit
set @figInWords=john.fig2words(@numUnitKount) + ' ' + john.fig2words (@numUnit)
+ ' ' + case
when @remainder <=0 then ''
when @remainder < 100 then 'and ' + john.fig2words(@remainder)
else john.fig2words(@remainder)
end
return(@figInWords)
end
else
begin
set @remainder=@FigInNum-@numUnit
set @figInWords=john.fig2words(@numUnit) + ' ' + john.fig2words(@remainder)
return(@figInWords)
end
return(@figInWords)
end
go
create table units_table
(
num_unit int primary key,
InWords varchar(255)
)
go
insert into
units_table
SELECT 0,'zero'
UNION ALL
SELECT 1,'one'
UNION ALL
SELECT 2,'two'
UNION ALL
SELECT 3,'three'
UNION ALL
SELECT 4,'four'
UNION ALL
SELECT 5,'five'
UNION ALL
SELECT 6,'six'
UNION ALL
SELECT 7,'seven'
UNION ALL
SELECT 8,'eight'
UNION ALL
SELECT 9,'nine'
UNION ALL
SELECT 10,'ten'
UNION ALL
SELECT 11,'eleven'
UNION ALL
SELECT 12,'twelve'
UNION ALL
SELECT 13,'thirteen'
UNION ALL
SELECT 14,'fourteen'
UNION ALL
SELECT 15,'fifteen'
UNION ALL
SELECT 16,'sixteen'
UNION ALL
SELECT 17,'seventeen'
UNION ALL
SELECT 18,'eighteen'
UNION ALL
SELECT 19,'nineteen'
UNION ALL
SELECT 20,'twenty'
UNION ALL
SELECT 30,'thirty'
UNION ALL
SELECT 40,'fourty'
UNION ALL
SELECT 50,'fifty'
UNION ALL
SELECT 60,'sixty'
UNION ALL
SELECT 70,'seventy'
UNION ALL
SELECT 80,'eighty'
UNION ALL
SELECT 90,'ninety'
UNION ALL
SELECT 100,'hundred'
UNION ALL
SELECT 1000,'thousand'
UNION ALL
SELECT 1000000,'million'
UNION ALL
SELECT 1000000000,'billion'
Rezvans Modified version
"Razvan Socol" <rsocol@fx.ro>
I tried your script for converting numeric figures into words and it seems good. I have a few suggestions:
1. You should prefix negative numbers with "minus"
2. You should return "zero" for 0.
3. For figures like 200000 or 1500000 you should remove the extra space between "hundred" and "thousand"
(now it returns: "one million five hundred thousand")
4. For 100 (or 1000, etc) you should return "one hundred" instead of "hundred".
Here is how I would do it:
ALTER FUNCTION fig2words (@FigInNum int) RETURNS varchar(255) AS
BEGIN
DECLARE @numUnit int
DECLARE @numUnitKount int
DECLARE @remainder int
DECLARE @figInWords varchar(255)
IF @FigInNum<0 BEGIN
SET @figInWords='minus '+dbo.fig2words(- @FigInNum)
RETURN @figInWords
END
SELECT @numUnit=MAX(num_unit) FROM units_table WHERE num_unit <= @FigInNum
IF @numUnit=@FigInNum BEGIN
SELECT @figInWords=CASE WHEN @FigInNum>=100 THEN 'one ' ELSE '' END
+ InWords FROM units_table WHERE num_unit = @FigInNum
RETURN @figInWords
END
IF @numUnit >=100 BEGIN
SET @numUnitKount=@FigInNum/@numUnit
SET @remainder=@FigInNum%@numUnit
SELECT @figInWords=dbo.fig2words(@numUnitKount) + ' ' + InWords
FROM units_table WHERE num_unit = @numUnit
IF @remainder>0
SET @figInWords = @figInWords
+ CASE WHEN @remainder<100 THEN ' and ' ELSE ' ' END
+ dbo.fig2words(@remainder)
END
ELSE BEGIN
SET @remainder=@FigInNum-@numUnit
SET @figInWords=dbo.fig2words(@numUnit)
IF @remainder>0
SET @figInWords = @figInWords + ' ' + dbo.fig2words(@remainder)
END
RETURN(@figInWords)
END
It is a little less readable, but I think that it gives better results.
The performance is good, but not very good. It takes 7 seconds on a 366MHz system to run the following:
SELECT Quantity*UnitPrice, dbo.fig2words(Quantity*UnitPrice) FROM Northwind.dbo.[Order Details]
That table has 2155 rows. It's not that my computer is too slow, because
SELECT Quantity*UnitPrice FROM Northwind.dbo.[Order Details]
only takes 13 miliseconds.
But I must recognize that this function will usualy be executed for only one row, and in this case it's fast enough (less than 20 miliseconds).
Razvan 
