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

Rate

Share

Share

Rate