MS SQL Function for Convert Number to Word

-- SQL number to words - SQL number to English - convert numbers into words

-- Translate number to text - Translate number to English - SQL number to check printing


-- SQL user-defined function - UDF - SQL scalar-valued function - SQL number to text

-- SQL numeric to words - integer to English - sql convert number to string
CREATE FUNCTION fnNumberToWords(@Number as BIGINT)
RETURNS VARCHAR(1024)
AS
BEGIN
DECLARE @Below20 TABLE (ID int identity(0,1), Word varchar(32))
DECLARE @Below100 TABLE (ID int identity(2,1), Word varchar(32))
DECLARE @English varchar(1024)
INSERT @Below20 (Word) VALUES ( 'Zero')
INSERT @Below20 (Word) VALUES ('One')
INSERT @Below20 (Word) VALUES ( 'Two' )
INSERT @Below20 (Word) VALUES ( 'Three')
INSERT @Below20 (Word) VALUES ( 'Four' )
INSERT @Below20 (Word) VALUES ( 'Five' )
INSERT @Below20 (Word) VALUES ( 'Six' )
INSERT @Below20 (Word) VALUES ( 'Seven' )
INSERT @Below20 (Word) VALUES ('Eight')
INSERT @Below20 (Word) VALUES ( 'Nine')
INSERT @Below20 (Word) VALUES ( 'Ten')
INSERT @Below20 (Word) VALUES ( 'Eleven' )
INSERT @Below20 (Word) VALUES ( 'Twelve' )
INSERT @Below20 (Word) VALUES ( 'Thirteen' )
INSERT @Below20 (Word) VALUES ( 'Fourteen')
INSERT @Below20 (Word) VALUES ( 'Fifteen' )
INSERT @Below20 (Word) VALUES ('Sixteen' )
INSERT @Below20 (Word) VALUES ( 'Seventeen')
INSERT @Below20 (Word) VALUES ('Eighteen' )
INSERT @Below20 (Word) VALUES ( 'Nineteen' )

INSERT @Below100 VALUES ('Twenty')
INSERT @Below100 VALUES ('Thirty')
INSERT @Below100 VALUES ('Forty')
INSERT @Below100 VALUES ('Fifty')
INSERT @Below100 VALUES ('Sixty')
INSERT @Below100 VALUES ('Seventy')
INSERT @Below100 VALUES ('Eighty')
INSERT @Below100 VALUES ('Ninety')
set @English = ( SELECT Case WHEN @Number = 0 THEN ''
WHEN @Number BETWEEN 1 AND 19
THEN (SELECT Word FROM @Below20 WHERE ID=@Number)
WHEN @Number BETWEEN 20 AND 99
THEN (SELECT Word FROM @Below100 WHERE ID=@Number/10)+ '-' +
dbo.fnNumberToWords( @Number % 10)
WHEN @Number BETWEEN 100 AND 999
THEN (dbo.fnNumberToWords( @Number / 100))+' Hundred '+
dbo.fnNumberToWords( @Number % 100)
WHEN @Number BETWEEN 1000 AND 999999
THEN (dbo.fnNumberToWords( @Number / 1000))+' Thousand '+
dbo.fnNumberToWords( @Number % 1000)
WHEN @Number BETWEEN 1000000 AND 999999999
THEN (dbo.fnNumberToWords( @Number / 1000000))+' Million '+
dbo.fnNumberToWords( @Number % 1000000)
WHEN @Number BETWEEN 1000000000 AND 999999999999
THEN (dbo.fnNumberToWords( @Number / 1000000000))+' Billion '+
dbo.fnNumberToWords( @Number % 1000000000)
WHEN @Number BETWEEN 1000000000000 AND 999999999999999
THEN (dbo.fnNumberToWords( @Number / 1000000000000))+' Trillion '+
dbo.fnNumberToWords( @Number % 1000000000000)
WHEN @Number BETWEEN 1000000000000000 AND 999999999999999999
THEN (dbo.fnNumberToWords( @Number / 1000000000000000))+' Quadrillion '+
dbo.fnNumberToWords( @Number % 1000000000000000)
WHEN @Number BETWEEN 1000000000000000000 AND 999999999999999999999
THEN (dbo.fnNumberToWords( @Number / 1000000000000000000))+' Quintillion '+
dbo.fnNumberToWords( @Number % 1000000000000000000)
ELSE ' INVALID INPUT' END
)

SELECT @English = RTRIM(@English)
SELECT @English = RTRIM(LEFT(@English,len(@English)-1))
WHERE RIGHT(@English,1)='-'
RETURN (@English)
END

GO



-- Test number to English translation

SELECT NumberInEnglish=dbo.fnNumberToWords ( 18)

SELECT NumberInEnglish=dbo.fnNumberToWords ( 67)

SELECT NumberInEnglish=dbo.fnNumberToWords ( 947)

-- Nine Hundred Forty-Seven

SELECT NumberInEnglish=dbo.fnNumberToWords ( 984261)

-- Nine Hundred Eighty-Four Thousand Two Hundred Sixty-One

SELECT NumberInEnglish=dbo.fnNumberToWords ( 777999888)

/* Seven Hundred Seventy-Seven Million Nine Hundred Ninety-Nine Thousand

Eight Hundred Eighty-Eight */

SELECT NumberInEnglish=dbo.fnNumberToWords ( 222777999888)

SELECT NumberInEnglish=dbo.fnNumberToWords ( 555222777999888)

SELECT NumberInEnglish=dbo.fnNumberToWords ( 7446744073709551616)

/*

Seven Quintillion Four Hundred Forty-Six Quadrillion Seven Hundred

Forty-Four Trillion Seventy-Three Billion Seven Hundred Nine Million

Five Hundred Fifty-One Thousand Six Hundred Sixteen

*/

GO