Number to word

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


CREATE FUNCTION dbo.NumToWord( @Number int )
RETURNS VARCHAR(255)
AS
BEGIN


DECLARE @Result VARCHAR(255), @Word VARCHAR(255), @Group VARCHAR(255)
DECLARE @Start int, @End int, @Mid int, @Digit VARCHAR(2), @Balance VARCHAR(20)


IF @Number = 0 RETURN 'Zero'


SELECT @Result = '', @Word = '', @Group = ''


SET @Balance = @Number
SET @Balance = REPLACE(@Balance,',','')
SET @Mid = LEN(@Balance) % 3 
IF @Mid > 0 SET @Balance = REPLICATE('0',3-@Mid) + @Balance -- pad left with zeroes to a multiple of 3


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


SET @Word = 
CASE 
WHEN @Mid = 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 (@End+2) / 3 = 2 THEN ' Thousand'
WHEN (@Group <> '' OR @Digit <> '0') AND (@End+2) / 3 = 3 THEN ' Million'
WHEN (@Group <> '' OR @Digit <> '0') AND (@End+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 (@End+2) / 3 = 2 THEN ' Thousand'
WHEN (@Group <> '' OR @Digit <> '00') AND (@End+2) / 3 = 3 THEN ' Million'
WHEN (@Group <> '' OR @Digit <> '00') AND (@End+2) / 3 = 4 THEN ' Billion'
ELSE ''
END
WHEN @Mid = 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 @Mid = 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) 


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


SET @Start = @Start + 1
SET @End = LEN(@Balance)-@Start+1
SET @Mid = @Start % 3 
IF @Mid = 1 SET @Group = '' 


END


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


END