Spell out Numbers

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

-- Thhe 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

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')