Monday, 3 June 2013

Sql Server Query for convert number to words

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

No comments:

Post a Comment