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