A Number To Words function Version 2

				
--**************************************
-- Name: A Number To Words function Version 2
-- Description:Converts a numeric value to words, i.e, 123 will return ONE HUNDRED TWENTY THREE
-- By: Pating sa Dagat
--
-- 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 functions.
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=1067&lngWId=5--for details.--**************************************

CREATE FUNCTION NumToWords_Ver2(@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)
DECLARE @nullStr int
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)
SET @place = @len
WHILE @place > 0
BEGIN	
	SET @place = @place - 1
	SET @nStr = NULL
	SET @digit = SUBSTRING(@cNum, @len-@place, 1)
	IF (@place+1) % 3 = 1 --One's place
	BEGIN
	SELECT @nStr = NumStr FROM @tblNum WHERE Num = @digit
	IF @nStr IS NOT NULL
			SET @hundred = @nStr
		IF LEN(@hundred) > 0
		SET @res = @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
		SET @hundred = ''
	END
	IF (@place+1) % 3 = 0 --Hundred's place
	BEGIN
	SELECT @nStr = NumStr FROM @tblNum WHERE Num = @digit
	IF @nStr IS NOT NULL
			SET @hundred = @nStr + ' HUNDRED'
		SET @tens = SUBSTRING(@cNum, @len-@place+1, 2)
		IF LEN(@hundred) > 0 AND (@tens = '' OR @tens = '00')
		SET @res = @res + @hundred + CASE (@place+1) / 3
			WHEN 0 THEN ''
			WHEN 1 THEN ''
			WHEN 2 THEN ' THOUSAND'
			WHEN 3 THEN ' MILLION'
			WHEN 4 THEN ' BILLION'
			WHEN 5 THEN ' TRILLION'
			WHEN 6 THEN ' QUADRILLION'
			WHEN 7 THEN ' QUINTILLION'
		END
		ELSE
			SET @res = @res + @hundred
		SET @hundred = ''
	END
	ELSE IF (@place+1) % 3 = 2 --Ten's place
	BEGIN	
		SET @tens = SUBSTRING(@cNum, @len-@place, 2)
	SELECT @nStr = NumStr FROM @tblNum WHERE Num = @tens
	IF @nStr IS NULL
		BEGIN
			SELECT @nStr = NumStr FROM @tblNum WHERE Num = @digit * 10
			SET @digit = SUBSTRING(@cNum, @len-@place+1, 1)
			SELECT @nStr = @nStr + NumStr FROM @tblNum WHERE Num = @digit
			
		END
		SET @hundred = @nStr
		IF LEN(@hundred) > 0 
		SET @res = @res + @hundred + CASE (@place+1) / 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 
		SET @place = @place - 1
		SET @hundred = ''
	END
	
END
RETURN @res
END