SQL Server T-SQL User-Defined Function of the Week

Convert Numbers to Words

Volume 1 Number 46    September 30, 2003

Check out the UDF Frequently Asked Questions at:
http://www.novicksoftware.com/UdfOfWeek/UDF_FAQ.htm

To ask a UDF related question or to contribute a UDF,
send e-mail to udf@NovickSoftware.com

Sign up for this newsletter at:
http://www.novicksoftware.com/UdfOfWeek/UDFofWeekSignup.htm

Reader Srinivas Sampath has contributed this week's UDF, which
spells out a number in words.  So if the input is 87 the UDF 
returns "Eighty Seven".  It's based on a stored procedure that
he's published with the same functionality.  You can find the
procedure at:
http://www32.brinkster.com/srisamp/sqlArticles/article_26.htm

Here's the CREATE FUNCTION Script:
/------- Start copying below this line -------------------------\
SET QUOTED_IDENTIFIER ON 
SET ANSI_NULLS ON 
GO

IF (OBJECT_ID('dbo.NumberToWords') IS NOT NULL)
	DROP FUNCTION dbo.NumberToWords
GO
CREATE FUNCTION dbo.NumberToWords
(
	@tnNumber	INT
) RETURNS VARCHAR(8000) AS
BEGIN
	-- Make sure that we convert only for 1..99
	IF (@tnNumber < 1 OR @tnNumber > 99)
	BEGIN
		RETURN '(Invalid Number. Input must be between 1 and 99)' 
	END

	-- Start of logic.
	-- Declare some local variables
	DECLARE @numbersTable TABLE (number INT, word VARCHAR(10))
	DECLARE @outputString VARCHAR(8000)
	DECLARE @word VARCHAR(50)
	DECLARE @remainder INT
	DECLARE @counter INT

	-- Initialize the variables
	SET @outputString = ''
	SET @counter = 0

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

	-- If the input number is < 20, we need some special processing
	IF (@tnNumber < 20)
	BEGIN
		SELECT @word = word FROM @numbersTable
			WHERE number = @tnNumber
		SET @outputString = @word
	END
	ELSE
	BEGIN
		WHILE (@tnNumber != 0)
		BEGIN
			-- Get the remainder of the result
			SET @remainder = (@tnNumber % 10) *
				CASE WHEN @counter = 0 THEN 1 ELSE @counter * 10 END
	
			-- Locate this value in our lookup table
			SELECT @word = word FROM @numbersTable
				WHERE number = @remainder
			SET @outputString = ISNULL(@word, '') + ' ' + @outputString
	
			-- Truncate the original number
			SET @tnNumber = @tnNumber / 10
			SET @counter = @counter + 1
		END
	END

	-- Return the result
	RETURN (@outputString)
END

GRANT EXEC on dbo.NumberToWords to PUBLIC
GO
\-------Stop copying above this line ---------------------------/

To test the UDF, requires only a simple script.  And let's not 
mess around.  The function works only on the range of numbers
between 1 and 99 so let's test them all.  While we're at it, I've
added one number on either side of the input range to 
test the error handling of the UDF.

/------- Start copying below this line -------------------------\
DECLARE @i int
SET @I = 0

WHILE @I <= 100 BEGIN
    PRINT right(' ' + convert(varchar, @i), 2) + ' ' + dbo.NumberToWords (@i)
    SET @I = @I + 1
END
GO
\-------Stop copying above this line ---------------------------/
(Results)
 #  Words
-- -----------------------
 0 (Invalid Number. Input must be between 1 and 99)
 1 One
 2 Two
 3 Three
 4 Four
 5 Five
 6 Six
 7 Seven
 8 Eight
 9 Nine
10 Ten
11 Eleven
12 Twelve
13 Thirteen
14 Fourteen
15 Fifteen
16 Sixteen
17 Seventeen
18 Eighteen
19 Nineteen
20 Twenty  
21 Twenty One 
22 Twenty Two 
23 Twenty Three 
24 Twenty Four 
25 Twenty Five 
26 Twenty Six 
27 Twenty Seven 
28 Twenty Eight 
29 Twenty Nine 
30 Thirty  
31 Thirty One 
32 Thirty Two 
33 Thirty Three 
34 Thirty Four 
35 Thirty Five 
36 Thirty Six 
37 Thirty Seven 
38 Thirty Eight 
39 Thirty Nine 
40 Forty  
41 Forty One 
42 Forty Two 
43 Forty Three 
44 Forty Four 
45 Forty Five 
46 Forty Six 
47 Forty Seven 
48 Forty Eight 
49 Forty Nine 
50 Fifty  
51 Fifty One 
52 Fifty Two 
53 Fifty Three 
54 Fifty Four 
55 Fifty Five 
56 Fifty Six 
57 Fifty Seven 
58 Fifty Eight 
59 Fifty Nine 
60 Sixty  
61 Sixty One 
62 Sixty Two 
63 Sixty Three 
64 Sixty Four 
65 Sixty Five 
66 Sixty Six 
67 Sixty Seven 
68 Sixty Eight 
69 Sixty Nine 
70 Seventy  
71 Seventy One 
72 Seventy Two 
73 Seventy Three 
74 Seventy Four 
75 Seventy Five 
76 Seventy Six 
77 Seventy Seven 
78 Seventy Eight 
79 Seventy Nine 
80 Eighty  
81 Eighty One 
82 Eighty Two 
83 Eighty Three 
84 Eighty Four 
85 Eighty Five 
86 Eighty Six 
87 Eighty Seven 
88 Eighty Eight 
89 Eighty Nine 
90 Ninety  
91 Ninety One 
92 Ninety Two 
93 Ninety Three 
94 Ninety Four 
95 Ninety Five 
96 Ninety Six 
97 Ninety Seven 
98 Ninety Eight 
99 Ninety Nine 
00 (Invalid Number. Input must be between 1 and 99)

(End of results)

Because this function returns a character string result, the
author has made the choice to return the error message as the
return value of the function.

This is the first issue of the T-SQL User-Defined Function of the
Week newsletter to be based on a user contribution.  I'm glad
to have them along with any ideas or problems that you have that
might be UDF related.  Thanks Srinivas!

Do you have a great UDF that you'd like to share?  Or maybe you
have a T-SQL problem that you think could be solved by a UDF
but you don't know how? Send them to:

UDF@NovickSoftware.com 

and they might be published in this newsletter.  I try and 
respond to every request that I get.  

Thanks,  
Andrew Novick
+--------------------------------------------------------------+