Sql Server Query
First Create a Table of name Sequence
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 a 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
First Create a Table of name Sequence
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 a 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
No comments:
Post a Comment