CONVERT AMOUNT(NUMBER) TO WORD(STRING) USING SQL SERVER

CONVERT AMOUNT(NUMBER) TO WORD(STRING) USING SQL SERVER

_______________________________________________________

 

/*TABLE CREATION*/

 CREATE TABLE  TblCurrencyBelow20  (ID INT IDENTITY(0,1), Word VARCHAR(32))

 CREATE TABLE  TblCurrencyBelow100 (ID INT IDENTITY(2,1), Word VARCHAR(32))

 

 /*VALUE INSERT*/

    INSERT TblCurrencyBelow20 (Word) VALUES ('ZERO')

    INSERT TblCurrencyBelow20 (Word) VALUES ('ONE')

    INSERT TblCurrencyBelow20 (Word) VALUES ( 'TWO' )

    INSERT TblCurrencyBelow20 (Word) VALUES ( 'THREE')

    INSERT TblCurrencyBelow20 (Word) VALUES ( 'FOUR' )

    INSERT TblCurrencyBelow20 (Word) VALUES ( 'FIVE' )

    INSERT TblCurrencyBelow20 (Word) VALUES ( 'SIX' )

    INSERT TblCurrencyBelow20 (Word) VALUES ( 'SEVEN' )

    INSERT TblCurrencyBelow20 (Word) VALUES ( 'EIGHT')

    INSERT TblCurrencyBelow20 (Word) VALUES ( 'NINE')

    INSERT TblCurrencyBelow20 (Word) VALUES ( 'TEN')

    INSERT TblCurrencyBelow20 (Word) VALUES ( 'ELEVEN' )

    INSERT TblCurrencyBelow20 (Word) VALUES ( 'TWELVE' )

    INSERT TblCurrencyBelow20 (Word) VALUES ( 'THIRTEEN' )

    INSERT TblCurrencyBelow20 (Word) VALUES ( 'FOURTEEN')

    INSERT TblCurrencyBelow20 (Word) VALUES ( 'FIFTEEN' )

    INSERT TblCurrencyBelow20 (Word) VALUES ( 'SIXTEEN' )

    INSERT TblCurrencyBelow20 (Word) VALUES ( 'SEVENTEEN')

    INSERT TblCurrencyBelow20 (Word) VALUES ( 'EIGHTEEN' )

    INSERT TblCurrencyBelow20 (Word) VALUES ( 'NINETEEN' )

 

    INSERT TblCurrencyBelow100 VALUES ('TWENTY')

    INSERT TblCurrencyBelow100 VALUES ('THIRTY')

    INSERT TblCurrencyBelow100 VALUES ('FORTY')

    INSERT TblCurrencyBelow100 VALUES ('FIFTY')

    INSERT TblCurrencyBelow100 VALUES ('SIXTY')

    INSERT TblCurrencyBelow100 VALUES ('SEVENTY')

    INSERT TblCurrencyBelow100 VALUES ('EIGHTY')

    INSERT TblCurrencyBelow100 VALUES ('NINETY')

 

/*CREATE FUNCTION FOR RETURN AMOUNT IN WORD*/

CREATE FUNCTION fnNumberToWords

(

    @Number AS BIGINT

) RETURNS VARCHAR(MAX)

AS

BEGIN

     DECLARE @BelowHundred AS VARCHAR(126)

 

    IF @Number > 99

    BEGIN

        SELECT @belowHundred = dbo.fnNumberToWords( @Number % 100)

    END

 

    DECLARE @NumberInWords VARCHAR(MAX)

    SET @NumberInWords  =

    (

      SELECT

        CASE

            WHEN @Number = 0 THEN  ''

 

            WHEN @Number BETWEEN 1 AND 19

                THEN (SELECT Word FROM TblCurrencyBelow20 WITH(NOLOCK) WHERE ID=@Number)

 

            WHEN @Number BETWEEN 20 AND 99

                THEN (SELECT Word FROM TblCurrencyBelow100 WITH(NOLOCK) WHERE ID=@Number/10)+ '-' + dbo.fnNumberToWords( @Number % 10)

 

            WHEN @Number BETWEEN 100 AND 999

                THEN (dbo.fnNumberToWords( @Number / 100)) + ' HUNDRED '+

                        CASE

                            WHEN @belowHundred <> ''

                                THEN 'AND ' + @belowHundred else @belowHundred

                        END

 

            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)

           

            ELSE ' INVALID INPUT'

        END

    )

 

    SELECT @NumberInWords = RTRIM(@NumberInWords)

 

    SELECT @NumberInWords = RTRIM(LEFT(@NumberInWords,LEN(@NumberInWords)-1)) WHERE RIGHT(@NumberInWords,1)='-'

 

    RETURN (@NumberInWords)

 

END

 

/*OUTPU*/

SELECT DBO.fnNumberToWords(1) AS AMOUNT_IN_WORD

AMOUNT_IN_WORD

ONE

 

SELECT DBO.fnNumberToWords(37) AS AMOUNT_IN_WORD

AMOUNT_IN_WORD

THIRTY-SEVEN

 

SELECT DBO.fnNumberToWords(178) AS AMOUNT_IN_WORD

AMOUNT_IN_WORD

ONE HUNDRED AND SEVENTY-EIGHT

 

SELECT DBO.fnNumberToWords(1345) AS AMOUNT_IN_WORD

AMOUNT_IN_WORD

ONE THOUSAND THREE HUNDRED AND FORTY-FIVE