Technical Article

Two scripts to spell out numbers in words

,

This script defines two user functions which will "translate" a number into its corresponding English words. The scripts can be used to generate unique character test data or for generating output suitable for speech synthesis.

The first, fnSpellInteger takes an integer argument and returns a VARCHAR(100). For example,

  • fnSpellInteger(10129) ==> "Ten Thousand One Hundred Twenty-Nine"

The second function, fnSpellNumber, is more general and takes a number with an optional decimal value as a VARCHAR(20) string. For example,

  • fnSpellNumber('15.99') ==> "Fifteen and Ninety-Nine Hundredths"

Test queries:

SELECT 0,MASTER.DBO.fnSpellInteger(0)
SELECT 9,MASTER.DBO.fnSpellInteger(9)
SELECT 19,MASTER.DBO.fnSpellInteger(19)
SELECT 29,MASTER.DBO.fnSpellInteger(29)
SELECT 129,MASTER.DBO.fnSpellInteger(129)
SELECT 1929,MASTER.DBO.fnSpellInteger(1929)
SELECT 10029,MASTER.DBO.fnSpellInteger(10029)
SELECT 10129,MASTER.DBO.fnSpellInteger(10129)
SELECT 192129,MASTER.DBO.fnSpellInteger(192129)
SELECT 1092129,MASTER.DBO.fnSpellInteger(1092129)
SELECT 1092.129,MASTER.DBO.fnSpellInteger(1092.129) -- Use fnSpellNumber for this

SELECT ' ', master.dbo.fnSpellNumber(' ')
SELECT 'a', master.dbo.fnSpellNumber('a')
SELECT '1a', master.dbo.fnSpellNumber('1a')
SELECT 'a1', master.dbo.fnSpellNumber('a1')
SELECT '1. ', master.dbo.fnSpellNumber('1. ')
SELECT '1..', master.dbo.fnSpellNumber('1..')
SELECT '1 .', master.dbo.fnSpellNumber('1 .')
SELECT '. 1', master.dbo.fnSpellNumber('. 1')
SELECT '1. 1', master.dbo.fnSpellNumber('1. 1')
SELECT '1.1.1', master.dbo.fnSpellNumber('1.1.1')
SELECT '.1A', master.dbo.fnSpellNumber('.1A')
SELECT '0.A1', master.dbo.fnSpellNumber('0.A1')
SELECT '0.1', master.dbo.fnSpellNumber('0.1')
SELECT '0.9', master.dbo.fnSpellNumber('0.9')
SELECT '0 ', master.dbo.fnSpellNumber('0 ')
SELECT ' 9 ', master.dbo.fnSpellNumber(' 9 ')

SELECT '92093', master.dbo.fnSpellNumber('92093')
SELECT '920093', master.dbo.fnSpellNumber('920093')
SELECT '15.002', master.dbo.fnSpellNumber('15.002')
SELECT '15.99', master.dbo.fnSpellNumber('15.99')
SELECT '.121', master.dbo.fnSpellNumber('.121')
SELECT '.120', master.dbo.fnSpellNumber('.120')
SELECT '15.9901', master.dbo.fnSpellNumber('15.9901')
SELECT '15.99001', master.dbo.fnSpellNumber('15.99001')
SELECT '15.990001', master.dbo.fnSpellNumber('15.990001')
SELECT '15.9900001', master.dbo.fnSpellNumber('15.9900001')
SELECT '99.99000001', master.dbo.fnSpellNumber('99.99000001')
SELECT '9900001.0', master.dbo.fnSpellNumber('9900001.0')
SELECT '99000001.99000001', master.dbo.fnSpellNumber('99000001.99000001')
SELECT '00110.2000', master.dbo.fnSpellNumber('00110.2000')
SELECT '00110', master.dbo.fnSpellNumber('00110')

SET NOCOUNT ON

-- =============================================
-- Author:	Copyright 2009 Anthony Zackin
-- Create date: 01-22-09
-- Description:	Converts an integer into words, viz., 
--		master.dbo.fnSpellInteger(10129) ==> "Ten Thousand One Hundred Twenty-Nine"
-- =============================================
USE MASTER

IF OBJECT_ID('dbo.fnSpellInteger') IS NOT NULL DROP FUNCTION fnSpellInteger
GO

CREATE FUNCTION dbo.fnSpellInteger ( @number int )
RETURNS VARCHAR(100)
AS
BEGIN
	-- For debugging outside of the UDF: DECLARE @debug bit  SET @debug = 0

	DECLARE @result VARCHAR(100), @word VARCHAR(100), @group VARCHAR(100)
	DECLARE @i int, @j int, @m int, @digit VARCHAR(2), @cn VARCHAR(20)

	IF @number = 0 RETURN 'Zero'

	SELECT @result = '', @word = '', @group = ''
	
	SET @cn = @number
	SET @cn = REPLACE(@cn,',','')
	SET @m = LEN(@cn) % 3 
	IF @m > 0 SET @cn = REPLICATE('0',3-@m) + @cn				-- Left pad with zeroes to a multiple of 3

	SET @i = 1
	SET @j = LEN(@cn)-@i+1
	SET @m = @i % 3
	WHILE @i <= LEN(@cn)
	BEGIN
		-- @i is 1 origin index into numeric string while @m = @i modulo 3
		-- If the middle digit of each group of 3 is a '1' then this is a 'Ten' or a '...teen'
		IF @m = 2 AND SUBSTRING(@cn,@i,1) = '1' 
		BEGIN
			SET @digit = SUBSTRING(@cn,@i,2)
			-- Skip rightmost digit of 3 if processing teens 
			SET @i = @i + 1
		END
		ELSE 
			SET @digit = SUBSTRING(@cn,@i,1)

		SET @word = 
		CASE 
			WHEN @m = 0 THEN									-- Rightmost digit of group of 3
				CASE @digit 
					WHEN '0' THEN ''
					WHEN '1' THEN 'One'
					WHEN '2' THEN 'Two'
					WHEN '3' THEN 'Three'
					WHEN '4' THEN 'Four'
					WHEN '5' THEN 'Five'
					WHEN '6' THEN 'Six'
					WHEN '7' THEN 'Seven'
					WHEN '8' THEN 'Eight'
					WHEN '9' THEN 'Nine'
				END + 
				CASE 
					WHEN (@group <> '' OR @digit <> '0') AND (@j+2) / 3 = 2 THEN ' Thousand'
					WHEN (@group <> '' OR @digit <> '0') AND (@j+2) / 3 = 3 THEN ' Million'
					WHEN (@group <> '' OR @digit <> '0') AND (@j+2) / 3 = 4 THEN ' Billion'
					ELSE ''
				END
			WHEN LEN(@digit) = 2 THEN							-- Special case when middle digit is a '1'
				CASE @digit 
					WHEN '10' THEN 'Ten'
					WHEN '11' THEN 'Eleven'
					WHEN '12' THEN 'Twelve'
					WHEN '13' THEN 'Thirteen'
					WHEN '14' THEN 'Fourteen'
					WHEN '15' THEN 'Fifteen'
					WHEN '16' THEN 'Sixteen'
					WHEN '17' THEN 'Seventeen'
					WHEN '18' THEN 'Eighteen'
					WHEN '19' THEN 'Nineteen'
				END +
				CASE 
					WHEN (@group <> '' OR @digit <> '00') AND (@j+2) / 3 = 2 THEN ' Thousand'
					WHEN (@group <> '' OR @digit <> '00') AND (@j+2) / 3 = 3 THEN ' Million'
					WHEN (@group <> '' OR @digit <> '00') AND (@j+2) / 3 = 4 THEN ' Billion'
					ELSE ''
				END
			WHEN @m = 2 THEN									-- Middle digit of group of 3
				CASE @digit 
					WHEN '2' THEN 'Twenty'
					WHEN '3' THEN 'Thirty'
					WHEN '4' THEN 'Forty'
					WHEN '5' THEN 'Fifty'
					WHEN '6' THEN 'Sixty'
					WHEN '7' THEN 'Seventy'
					WHEN '8' THEN 'Eighty'
					WHEN '9' THEN 'Ninety'
					ELSE ''
				END
			WHEN @m = 1 THEN									-- Leftmost digit of group of 3
				CASE @digit 
					WHEN '0' THEN ''
					WHEN '1' THEN 'One'
					WHEN '2' THEN 'Two'
					WHEN '3' THEN 'Three'
					WHEN '4' THEN 'Four'
					WHEN '5' THEN 'Five'
					WHEN '6' THEN 'Six'
					WHEN '7' THEN 'Seven'
					WHEN '8' THEN 'Eight'
					WHEN '9' THEN 'Nine'
				END + 
				CASE WHEN @digit <> '0' THEN ' Hundred' ELSE '' END
		END

		SET @group = @group + RTRIM(@word)						-- Group value

		IF @word <> '' 
		BEGIN
			DECLARE @prefix VARCHAR(20)
			IF CHARINDEX(' ',@word) > 0 SET @prefix = LEFT(@word,CHARINDEX(' ',@word)) ELSE SET @prefix = @word 
			IF RIGHT(@result,2) = 'ty' AND @prefix IN ('One','Two','Three','Four','Five','Six','Seven','Eight','Nine')
				SET @result = @result + '-' + LTRIM(@word) 
			ELSE
				SET @result = @result + ' ' + LTRIM(@word) 
		END
		-- The following needs to be outside of a UDF to work:
		--IF @debug = 1 SELECT @cn as 'Number', @i as '@i', @j as '@j', @m as '@m', @digit as '@digit', CAST(replace(@group,' ','`') AS CHAR(30)) as '@group', @word as '@word', @result as '@result'
		SET @i = @i + 1
		SET @j = LEN(@cn)-@i+1
		SET @m = @i % 3 
		IF @m = 1 SET @group = ''								-- Clear group value when starting a new one

	END

	IF @result = '' SET @result = '0'
	RETURN LTRIM(@Result)

END
GO


SET NOCOUNT ON

-- =============================================
-- Author:	Copyright 2009 Anthony Zackin
-- Create date: 01-22-09
-- Description:	Converts a string numeric expression into words, viz., 
--		master.dbo.fnSpellNumber('15.99') ==> "Fifteen and Ninety-Nine Hundredths"
-- Notes:	Uses fnSpellInteger to convert an integer into words
-- Example:	fnSpellNumber can be used to generate pseudo-random test character data
/*
	set nocount on
	declare @rand int, @i int
	set @rand = rand(131)*100
	set @i = 0

	while @i < 5
	begin
		set @i = @i + 1
		select 'insert(id,number,words) values(' + cast(@i as varchar(5)) + ',' +
			cast(@rand as varchar(5)) + ',''' + master.dbo.fnspellnumber(@rand) + ''')'
		set @rand = rand()*100
	end
*/
-- =============================================
USE MASTER

IF OBJECT_ID('master.dbo.fnSpellNumber') IS NOT NULL DROP FUNCTION fnSpellNumber
GO

CREATE FUNCTION dbo.fnSpellNumber ( @number varchar(20) )
RETURNS VARCHAR(200)
AS
--For debugging: declare @number varchar(20) set @number = '192.1'
BEGIN
	-- This is for use outside of a function: DECLARE @debug bit  SET @debug = 0	

	DECLARE @result varchar(200), @word varchar(100)
	DECLARE @i int, @intpart varchar(20), @decpart varchar(20)
	
	SET @word = LTRIM(RTRIM(@number))
	-- Check for a bad number, e.g., one with embedded spaces
	IF ISNUMERIC(@word) = 0 RETURN '<< NOT A NUMBER >>'

	SET @i = CHARINDEX('.', @word)
	-- Remove trailing zeroes for any decimal portion
	IF @i > 0 -- Number contains a decimal point
	BEGIN
		WHILE RIGHT(@word,1) = '0' SET @word = LEFT(@word,LEN(@word)-1)
		IF @word = '' SET @word = '0'
	END
	-- Insert a decimal point at the end if none was specified
	IF @i = 0 -- No decimal point
	BEGIN 
		SET @word = @number + '.' 
		SET @i = CHARINDEX('.', @word) 
	END

	SET @intpart = LEFT(@word,@i-1)	-- Extract the integer part of the number if any
	IF LEN(@intpart) > 0
		SET @result = master.dbo.fnSpellInteger(CAST(@intpart AS int))
	ELSE
		SET @result = ''

	-- Extract the decimal portion of the number
	SET @decpart = RIGHT(@word,LEN(@word)-@i)	-- @i is position of decimal point

	IF LEN(@decpart) > 0 
	BEGIN
		IF @result = 'Zero' 
			SET @result = '' 
		ELSE IF @result <> '' 
			SET @result = @result + ' and '

		SET @result = @result + master.dbo.fnSpellInteger(@decpart) +
			CASE LEN(@decpart) 
				WHEN 0 THEN ''
				WHEN 1 THEN ' Tenths'
				WHEN 2 THEN ' Hundredths'
				WHEN 3 THEN ' One-Thousandths'
				WHEN 4 THEN ' Ten-Thousandths'
				WHEN 5 THEN ' One-Hundred-Thousandths'
				WHEN 6 THEN ' One-Millionths'
				WHEN 7 THEN ' Ten-Millionths'
				WHEN 8 THEN ' One-Hundred-Millionths'
				WHEN 9 THEN ' One-Billionths'
			END 
		-- Check for a valid plural
		IF @decpart = 1 SET @result = LEFT(@result, LEN(@result)-1)	-- Remove last "s" for just 1 
	END	
	
	-- This is for use outside of a function: if @debug = 1 select @word as '@word', @i as '@i', @intpart as '@intpart', @decpart as '@decpart', @result as '@result'
	
	RETURN @result

END
GO

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.2 (5)

You rated this post out of 5. Change rating

Share

Tags

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.2 (5)

You rated this post out of 5. Change rating

Related content