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

Number to word in SQL

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET NOCOUNT ON
GO



CREATE FUNCTION dbo.udf_Num_ToWords (
@Number Numeric (38, 0) -- Input number with as many as 18 digits
) RETURNS VARCHAR(8000)
/*
* Converts a integer number as large as 34 digits into the
* equivalent words.  The first letter is capitalized.
*
* Attribution: Based on NumberToWords by Srinivas Sampath
*        as revised by Nick Barclay
*
* Example:
select dbo.udf_Num_ToWords (1234567890) + CHAR(10)
      +  dbo.udf_Num_ToWords (0) + CHAR(10)
      +  dbo.udf_Num_ToWords (123) + CHAR(10)
select dbo.udf_Num_ToWords(76543210987654321098765432109876543210)

DECLARE @i numeric (38,0)
SET @i = 0
WHILE @I <= 1000 BEGIN
    PRINT convert (char(5), @i)
            + convert(varchar(255), dbo.udf_Num_ToWords(@i))
    SET @I  = @i + 1
END
*
* Published as the T-SQL UDF of the Week Vol 2 #9 2/17/03
****************************************************************/
AS BEGIN

DECLARE @inputNumber VARCHAR(38)
DECLARE @NumbersTable TABLE (number CHAR(2), word VARCHAR(10))
DECLARE @outputString VARCHAR(8000)
DECLARE @length INT
DECLARE @counter INT
DECLARE @loops INT
DECLARE @position INT
DECLARE @chunk CHAR(3) -- for chunks of 3 numbers
DECLARE @tensones CHAR(2)
DECLARE @hundreds CHAR(1)
DECLARE @tens CHAR(1)
DECLARE @ones CHAR(1)

IF @Number = 0 Return 'Zero'
-- initialize the variables
SELECT @inputNumber = CONVERT(varchar(38), @Number)
     , @outputString = ''
     , @counter = 1
SELECT @length   = LEN(@inputNumber)
     , @position = LEN(@inputNumber) - 2
     , @loops    = LEN(@inputNumber)/3
-- make sure there is an extra loop added for the remaining numbers
IF LEN(@inputNumber) % 3 <> 0 SET @loops = @loops + 1
-- insert data for the numbers and words
INSERT INTO @NumbersTable   SELECT '00', ''
    UNION ALL SELECT '01', 'one'      UNION ALL SELECT '02', 'two'
    UNION ALL SELECT '03', 'three'    UNION ALL SELECT '04', 'four'
    UNION ALL SELECT '05', 'five'     UNION ALL SELECT '06', 'six'
    UNION ALL SELECT '07', 'seven'    UNION ALL SELECT '08', 'eight'
    UNION ALL SELECT '09', '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', 'ninety'

WHILE @counter <= @loops BEGIN
-- get chunks of 3 numbers at a time, padded with leading zeros
SET @chunk = RIGHT('000' + SUBSTRING(@inputNumber, @position, 3), 3)

IF @chunk <> '000' BEGIN
SELECT @tensones = SUBSTRING(@chunk, 2, 2)
     , @hundreds = SUBSTRING(@chunk, 1, 1)
     , @tens = SUBSTRING(@chunk, 2, 1)
     , @ones = SUBSTRING(@chunk, 3, 1)
-- If twenty or less, use the word directly from @NumbersTable
IF CONVERT(INT, @tensones) <= 20 OR @Ones='0' BEGIN
SET @outputString = (SELECT word
                                      FROM @NumbersTable
                                      WHERE @tensones = number)
                   + CASE @counter WHEN 1 THEN '' -- No name
                       WHEN 2 THEN ' thousand ' WHEN 3 THEN ' thousand '
                       WHEN 4 THEN ' billion '  WHEN 5 THEN ' trillion '
                       WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion '
                       WHEN 8 THEN ' sextillion '  WHEN 9 THEN ' septillion '
                       WHEN 10 THEN ' octillion '  WHEN 11 THEN ' nonillion '
                       WHEN 12 THEN ' decillion '  WHEN 13 THEN ' undecillion '
                       ELSE '' END
                               + @outputString
    END
ELSE BEGIN -- break down the ones and the tens separately

             SET @outputString = ' '
                            + (SELECT word
                                    FROM @NumbersTable
                                    WHERE @tens + '0' = number)
         + '-'
                             + (SELECT word
                                    FROM @NumbersTable
                                    WHERE '0'+ @ones = number)
                   + CASE @counter WHEN 1 THEN '' -- No name
                       WHEN 2 THEN ' thousand ' WHEN 3 THEN ' million '
                       WHEN 4 THEN ' billion '  WHEN 5 THEN ' trillion '
                       WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion '
                       WHEN 8 THEN ' sextillion '  WHEN 9 THEN ' septillion '
                       WHEN 10 THEN ' octillion '  WHEN 11 THEN ' nonillion '
                       WHEN 12 THEN ' decillion '   WHEN 13 THEN ' undecillion '
                       ELSE '' END
                            + @outputString
END
-- now get the hundreds
IF @hundreds <> '0' BEGIN
SET @outputString  = (SELECT word
                                      FROM @NumbersTable
                                      WHERE '0' + @hundreds = number)
            + ' hundred '
                                + @outputString
END
END

SELECT @counter = @counter + 1
     , @position = @position - 3
END
-- Remove any double spaces
SET @outputString = LTRIM(RTRIM(REPLACE(@outputString, '  ', ' ')))
SET @outputstring = UPPER(LEFT(@outputstring, 1)) + SUBSTRING(@outputstring, 2, 8000)


RETURN @outputString -- return the resultEND

--select dbo.udf_Num_ToWords(100)

Number to Word Conversion in Sql Server

DECLARE @i int, @temp char(1),  @s VARCHAR(20), @result VARCHAR(255),@N BIGINT
    set  @n=10 /// in this @n is the i mention here the 10 u are free to set any one on this location
    SELECT @s=convert(varchar(20), @n)
    SELECT @i=LEN(@s)
    SELECT @result=''
    WHILE (@i>0)
    BEGIN
        SELECT @temp=(SUBSTRING(@s,@i,1))
        IF ((LEN(@s)-@i) % 3)=1
        IF @temp='1'
        SELECT @result=CASE (SUBSTRING(@s,@i+1,1))
            WHEN '0' THEN 'ten'
            WHEN '1' THEN 'eleven'
            WHEN '2' THEN 'twelve'
            WHEN '3' THEN 'thirteen'
            WHEN '4' THEN 'fourteen'
            WHEN '5' THEN 'fifteen'
            WHEN '6' THEN 'sixteen'
            WHEN '7' THEN 'seventeen'
            WHEN '8' THEN 'eighteen'
            WHEN '9' THEN 'nineteen'
            END+' '+CASE
                    WHEN ((LEN(@s)-@i)=4) THEN 'thousand '
                    WHEN ((LEN(@s)-@i)=7) THEN 'million '
                    WHEN ((LEN(@s)-@i)=10) THEN 'billion '
                    WHEN ((LEN(@s)-@i)=13) THEN 'trillion '
                    WHEN ((LEN(@s)-@i)=16) THEN 'quadrillion '
                    ELSE ''
                    END+@result
        ELSE
        BEGIN
            SELECT @result=CASE (SUBSTRING(@s,@i+1,1))
                WHEN '0' THEN ''
                WHEN '1' THEN 'one'
                WHEN '2' THEN 'two'
                WHEN '3' THEN 'three'
                WHEN '4' THEN 'four'
                WHEN '5' THEN 'five'
                WHEN '6' THEN 'six'
                WHEN '7' THEN 'seven'
                WHEN '8' THEN 'eight'
                WHEN '9' THEN 'nine'
                END+' '+ CASE
                    WHEN ((LEN(@s)-@i)=4) THEN 'thousand '
                    WHEN ((LEN(@s)-@i)=7) THEN 'million '
                    WHEN ((LEN(@s)-@i)=10) THEN 'billion '
                    WHEN ((LEN(@s)-@i)=13) THEN 'trillion '
                    WHEN ((LEN(@s)-@i)=16) THEN 'quadrillion '
                    ELSE ''
                    END+@result
            SELECT @result=CASE @temp
                WHEN '0' THEN ''
                WHEN '1' THEN 'ten'
                WHEN '2' THEN 'twenty'
                WHEN '3' THEN 'thirty'
                WHEN '4' THEN 'fourty'
                WHEN '5' THEN 'fifty'
                WHEN '6' THEN 'sixty'
                WHEN '7' THEN 'seventy'
                WHEN '8' THEN 'eighty'
                WHEN '9' THEN 'ninety'
                END+' '+@result
        END
        IF (((LEN(@s)-@i) % 3)=2) OR (((LEN(@s)-@i) % 3)=0) AND (@i=1)
        BEGIN
        SELECT @result=CASE @temp
            WHEN '0' THEN ''
            WHEN '1' THEN 'one'
            WHEN '2' THEN 'two'
            WHEN '3' THEN 'three'
            WHEN '4' THEN 'four'
            WHEN '5' THEN 'five'
            WHEN '6' THEN 'six'
            WHEN '7' THEN 'seven'
            WHEN '8' THEN 'eight'
            WHEN '9' THEN 'nine'
            END +' '+CASE
                WHEN (@s='0') THEN 'zero'
                WHEN (@temp<>'0')AND( ((LEN(@s)-@i) % 3)=2) THEN 'hundred '
                ELSE ''
                END + CASE
                WHEN ((LEN(@s)-@i)=3) THEN 'thousand '
                WHEN ((LEN(@s)-@i)=6) THEN 'million '
                WHEN ((LEN(@s)-@i)=9) THEN 'billion '
                WHEN ((LEN(@s)-@i)=12) THEN 'trillion '
                WHEN ((LEN(@s)-@i)=15) THEN 'quadrillion '
                ELSE ''
                    END+ @result
        END
        SELECT @i=@i-1
    END
    select REPLACE(@result,'  ',' ')