how to write number to word function in sql server [closed]
How would one write a function in SQL Server to output a number in words?
input: 1
output: one
input: 129
output: one hundred twenty-nine
Consider using an auxiliary numbers table.
NB: This MS SQL
Create a Sequence table - This could include all the numbers you need or at least up to 999. I have limited it to the least but it adds extra logic.
CREATE TABLE [dbo].[Sequence]
(
seq INTEGER NOT NULL UNIQUE,
word [varchar](25) NOT NULL
)
INSERT INTO [Sequence] SELECT 0, ''
INSERT INTO [Sequence] SELECT 1, 'One'
INSERT INTO [Sequence] SELECT 2, 'Two'
INSERT INTO [Sequence] SELECT 3, 'Three'
INSERT INTO [Sequence] SELECT 4, 'Four'
INSERT INTO [Sequence] SELECT 5, 'Five'
INSERT INTO [Sequence] SELECT 6, 'Six'
INSERT INTO [Sequence] SELECT 7, 'Seven'
INSERT INTO [Sequence] SELECT 8, 'Eight'
INSERT INTO [Sequence] SELECT 9, 'Nine'
INSERT INTO [Sequence] SELECT 10, 'Ten'
INSERT INTO [Sequence] SELECT 11, 'Eleven'
INSERT INTO [Sequence] SELECT 12, 'Twelve'
INSERT INTO [Sequence] SELECT 13, 'Thirteen'
INSERT INTO [Sequence] SELECT 14, 'Fourteen'
INSERT INTO [Sequence] SELECT 15, 'Fifteen'
INSERT INTO [Sequence] SELECT 16, 'Sixteen'
INSERT INTO [Sequence] SELECT 17, 'Seventeen'
INSERT INTO [Sequence] SELECT 18, 'Eighteen'
INSERT INTO [Sequence] SELECT 19, 'Nineteen'
INSERT INTO [Sequence] SELECT 20, 'Twenty'
INSERT INTO [Sequence] SELECT 30, 'Thirty'
INSERT INTO [Sequence] SELECT 40, 'Forty'
INSERT INTO [Sequence] SELECT 50, 'Fifty'
INSERT INTO [Sequence] SELECT 60, 'Sixty'
INSERT INTO [Sequence] SELECT 70, 'Seventy'
INSERT INTO [Sequence] SELECT 80, 'Eighty'
INSERT INTO [Sequence] SELECT 90, 'Ninty'
Then Create the user defined function.
CREATE FUNCTION dbo.udf_NumToWords (
@num AS INTEGER
) RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @words AS VARCHAR(50)
IF @num = 0 SELECT @words = 'Zero'
ELSE IF @num < 20 SELECT @words = word FROM sequence WHERE seq = @num
ELSE IF @num < 100 (SELECT @words = TTens.word + ' ' + TUnits.word
FROM Sequence AS TUnits
CROSS JOIN Sequence AS TTens
WHERE TUnits.seq = (@num % 100) % 10
AND TTens.seq = (@num % 100) - (@num % 100) % 10
)
ELSE IF @num = 100 (SELECT @words = THundreds.word + ' Hundred'
FROM Sequence AS THundreds
WHERE THundreds.seq = (@num / 100)
)
ELSE IF @num < 1000 (
SELECT @words = THundreds.word + ' Hundred and '
+ TTens.word + ' ' + TUnits.word
FROM Sequence AS TUnits
CROSS JOIN Sequence AS TTens
CROSS JOIN Sequence AS THundreds
WHERE TUnits.seq = (@num % 100) % 10
AND TTens.seq = (@num % 100) - (@num % 100) % 10
AND THundreds.seq = (@num / 100)
)
ELSE IF @num = 1000 (SELECT @words = TThousand.word + ' Thousand'
FROM Sequence AS TThousand
WHERE TThousand.seq = (@num / 1000)
)
ELSE IF @num < 10000 (
SELECT @words = TThousand.word + ' Thousand '
+ THundreds.word + ' Hundred and '
+ TTens.word + ' ' + TUnits.word
FROM Sequence AS TUnits
CROSS JOIN Sequence AS TTens
CROSS JOIN Sequence AS THundreds
CROSS JOIN Sequence AS TThousand
WHERE TUnits.seq = (@num % 100) % 10
AND TTens.seq = (@num % 100) - (@num % 100) % 10
AND THundreds.seq = (@num / 100) - (@num / 1000) * 10
AND TThousand.seq = (@num / 1000)
)
ELSE SELECT @words = STR(@num)
RETURN @words
END
Now Test Function:
SELECT NumberAsWords = dbo.udf_NumToWords(888);
This is slight modification of above answer by Andrew (all credits to Andrew) but this will return results in sql query without using any function
WITH Sequence
AS (
SELECT 0 seq
,'' word
UNION ALL
SELECT 1
,'One'
UNION ALL
SELECT 2
,'Two'
UNION ALL
SELECT 3
,'Three'
UNION ALL
SELECT 4
,'Four'
UNION ALL
SELECT 5
,'Five'
UNION ALL
SELECT 6
,'Six'
UNION ALL
SELECT 7
,'Seven'
UNION ALL
SELECT 8
,'Eight'
UNION ALL
SELECT 9
,'Nine'
UNION ALL
SELECT 10
,'Ten'
UNION ALL
SELECT 11
,'Eleven'
UNION ALL
SELECT 12
,'Twelve'
UNION ALL
SELECT 13
,'Thirteen'
UNION ALL
SELECT 14
,'Fourteen'
UNION ALL
SELECT 15
,'Fifteen'
UNION ALL
SELECT 16
,'Sixteen'
UNION ALL
SELECT 17
,'Seventeen'
UNION ALL
SELECT 18
,'Eighteen'
UNION ALL
SELECT 19
,'Nineteen'
UNION ALL
SELECT 20
,'Twenty'
UNION ALL
SELECT 30
,'Thirty'
UNION ALL
SELECT 40
,'Forty'
UNION ALL
SELECT 50
,'Fifty'
UNION ALL
SELECT 60
,'Sixty'
UNION ALL
SELECT 70
,'Seventy'
UNION ALL
SELECT 80
,'Eighty'
UNION ALL
SELECT 90
,'Ninty'
)
,CTENumbers
AS (
SELECT 0 AS num --changezero to your starting number
UNION ALL
SELECT num + 1
FROM CTENumbers
WHERE num + 1 <= 255 --change 255 to upper limit
)
SELECT *
,CASE
WHEN num = 0
THEN 'Zero'
WHEN num < 20
THEN (
SELECT word
FROM sequence
WHERE seq = num
)
WHEN num < 100
THEN (
SELECT TTens.word + ' ' + TUnits.word
FROM Sequence AS TUnits
CROSS JOIN Sequence AS TTens
WHERE TUnits.seq = (num % 100) % 10
AND TTens.seq = (num % 100) - (num % 100) % 10
)
WHEN num < 1000
THEN (
SELECT THundreds.word + ' Hundred and ' + TTens.word + ' ' + TUnits.word
FROM Sequence AS TUnits
CROSS JOIN Sequence AS TTens
CROSS JOIN Sequence AS THundreds
WHERE TUnits.seq = (num % 100) % 10
AND TTens.seq = (num % 100) - (num % 100) % 10
AND THundreds.seq = (num / 100)
)
WHEN num = 1000
THEN (
SELECT TThousand.word + ' Thousand'
FROM Sequence AS TThousand
WHERE TThousand.seq = (num / 1000)
)
WHEN num < 10000
THEN (
SELECT TThousand.word + ' Thousand ' + THundreds.word + ' Hundred and ' + TTens.word + ' ' + TUnits.word
FROM Sequence AS TUnits
CROSS JOIN Sequence AS TTens
CROSS JOIN Sequence AS THundreds
CROSS JOIN Sequence AS TThousand
WHERE TUnits.seq = (num % 100) % 10
AND TTens.seq = (num % 100) - (num % 100) % 10
AND THundreds.seq = (num / 100) - (num / 1000) * 10
AND TThousand.seq = (num / 1000)
)
ELSE STR(num)
END Number
FROM CTENumbers
OPTION (MAXRECURSION 10000)