Author: Leon Tayson
URL: http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=1064&lngWId=5
Converts a numeric value to words, i.e, 123 will return ONE HUNDRED TWENTY THREE
--**************************************
--
-- Name: A Number To Words function
-- Description:Converts a numeric value
-- to words, i.e, 123 will return ONE HUNDR
-- ED TWENTY THREE
-- By: Leon Tayson
--
-- Inputs:@num - a numeric value
--
-- Returns:the number in words
--
-- Assumes:Copy and paste the code into
-- Query Analyzer and hit F5, then you can
-- use it just as you would use other SQL f
-- unctions.
/***************************************************
Leon C. Tayson
Converts a numeric value TO word
Sample usage:
SELECT dbo.NumToWords(123456789012345678)
****************************************************/
CREATE FUNCTION NumToWords
(@num numeric)
RETURNS varchar(1000)
AS
BEGIN
DECLARE @cNum varchar(20)
SET @cNum = @num
DECLARE @len int
SET @len = LEN(@Num)
DECLARE @place int
SET @place = 0
DECLARE @digit varchar(1), @tens varchar(2)
DECLARE @res varchar(1000)
SET @res = ''
DECLARE @tblNum TABLE
(Num int, NumStr varchar(20))
INSERT INTO @tblNum
SELECT 1, ' ONE' UNION SELECT 2, ' TWO' UNION
SELECT 3, ' THREE' UNION SELECT 4, ' FOUR' UNION
SELECT 5, ' FIVE' UNION SELECT 6, ' SIX' UNION
SELECT 7, ' SEVEN' UNION SELECT 8, ' EIGHT' UNION
SELECT 9, ' NINE' UNION SELECT 10, ' TEN' UNION
SELECT 11, ' ELEVEN' UNION SELECT 12, ' TWELVE' UNION
SELECT 13, ' THIRTEEN' UNION SELECT 14, ' FOURTEEN' UNION
SELECT 15, ' FIFTEEN' UNION SELECT 16, ' SIXTEEN' UNION
SELECT 17, ' SEVENTEEN' UNION SELECT 18, ' EIGHTEEN' UNION
SELECT 19, ' NINETEEN' UNION
SELECT 20, ' TWENTY' UNION SELECT 30, ' THIRTY' UNION
SELECT 40, ' FOURTY' UNION SELECT 50, ' FIFTY' UNION
SELECT 60, ' SIXTY' UNION SELECT 70, ' SEVENTY' UNION
SELECT 80, ' EIGHTY' UNION SELECT 90, ' NINETY'
DECLARE @hundred varchar(200)
SET @hundred = ''
DECLARE @nStr varchar(20)
WHILE @place < @len
BEGIN
SET @tens = ''
SET @nStr = NULL
SET @digit = SUBSTRING(@cNum, @len - @place, 1)
--SET @digit = SUBSTRING(@Num, @len - @p
-- lace, 1)
IF @place % 3 = 0-- one's place
BEGIN
-- Check ten's place
SET @tens = SUBSTRING(@cNum, @len - @place - 1, 2)
--SET @tens = SUBSTRING(@Num, @len - @pl
-- ace - 1, 2)
SELECT @nStr = NumStr FROM @tblNum WHERE Num = @tens
IF @nStr IS NOT NULL
SELECT @hundred = @hundred + @nStr, @place = @place + 1
ELSE
BEGIN
SELECT @nStr = NumStr FROM @tblNum WHERE Num = @digit
SET @hundred = ISNULL(@nStr, '') + @hundred
END
END
ELSE IF @place % 3 = 1-- Ten's place
BEGIN
SELECT @nStr = NumStr FROM @tblNum WHERE Num = @digit * 10--POWER(10, @place % 3)
SET @hundred = ISNULL(@nStr, '') + @hundred
END
IF @place % 3 = 2-- '000 place
BEGIN
SELECT @nStr = NumStr FROM @tblNum WHERE Num = @digit
IF @nStr IS NOT NULL
SET @hundred = @nStr + ' HUNDRED' + @hundred
--print '000 place'
IF LEN(@hundred) > 0
SET @res = @hundred +
CASE @place / 3
WHEN 0 THEN ''
WHEN 1 THEN ' THOUSAND'
WHEN 2 THEN ' MILLION'
WHEN 3 THEN ' BILLION'
WHEN 4 THEN ' TRILLION'
WHEN 5 THEN ' QUADRILLION'
WHEN 6 THEN ' QUINTILLION'
END + @res
SET @hundred = ''
END
SET @place = @place + 1
END
RETURN @res
END
