August 24, 2012

SQL SERVER – Query to Convert Numbers into Words

Convert Money Numbers into Strings

CREATE FUNCTION fnNumberToWords
(
    @Number AS BIGINT
) RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @Below20 TABLE (ID INT IDENTITY(0,1), Word VARCHAR(32))
    DECLARE @Below100 TABLE (ID INT IDENTITY(2,1), Word VARCHAR(32))
    DECLARE @BelowHundred AS VARCHAR(126) 
    
    INSERT @Below20 (Word) VALUES ('ZERO')
    INSERT @Below20 (Word) VALUES ('ONE')
    INSERT @Below20 (Word) VALUES ( 'TWO' )
    INSERT @Below20 (Word) VALUES ( 'THREE')
    INSERT @Below20 (Word) VALUES ( 'FOUR' )
    INSERT @Below20 (Word) VALUES ( 'FIVE' )
    INSERT @Below20 (Word) VALUES ( 'SIX' )
    INSERT @Below20 (Word) VALUES ( 'SEVEN' )
    INSERT @Below20 (Word) VALUES ( 'EIGHT')
    INSERT @Below20 (Word) VALUES ( 'NINE')
    INSERT @Below20 (Word) VALUES ( 'TEN')
    INSERT @Below20 (Word) VALUES ( 'ELEVEN' )
    INSERT @Below20 (Word) VALUES ( 'TWELVE' )
    INSERT @Below20 (Word) VALUES ( 'THIRTEEN' )
    INSERT @Below20 (Word) VALUES ( 'FOURTEEN')
    INSERT @Below20 (Word) VALUES ( 'FIFTEEN' )
    INSERT @Below20 (Word) VALUES ( 'SIXTEEN' )
    INSERT @Below20 (Word) VALUES ( 'SEVENTEEN')
    INSERT @Below20 (Word) VALUES ( 'EIGHTEEN' )
    INSERT @Below20 (Word) VALUES ( 'NINETEEN' )
 
    INSERT @Below100 VALUES ('TWENTY')
    INSERT @Below100 VALUES ('THIRTY')
    INSERT @Below100 VALUES ('FORTY')
    INSERT @Below100 VALUES ('FIFTY')
    INSERT @Below100 VALUES ('SIXTY')
    INSERT @Below100 VALUES ('SEVENTY')
    INSERT @Below100 VALUES ('EIGHTY')
    INSERT @Below100 VALUES ('NINETY')
 
    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 @Below20 WHERE ID=@Number)
 
            WHEN @Number BETWEEN 20 AND 99
                THEN (SELECT Word FROM @Below100 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

15 comments:

  1. Thanks for Gr8 Function

    ReplyDelete
  2. Amount 135,832.87 returns ONE HUNDRED AND THIRTY-FIVE THOUSAND EIGHT HUNDRED AND THIRTY-TWO .... Digit after decimal not convering...

    ReplyDelete
  3. Replies
    1. Why a 'bad function' because it wasnt written for indian rupees

      bad comment if you ask me - the function works well

      you obviously have vested interest in discrediting this work

      especially when the Stack post is a plagiarised copy from code found at https://www.sqlservercentral.com/Forums/Topic794134-149-1.aspx

      poor form balu and needs to be called out.

      Delete
  4. http://stackoverflow.com/questions/29362038/how-to-convert-numeric-value-to-indian-rupees-words-in-sql

    ReplyDelete
    Replies
    1. Thanks Balu.. it really helped me.

      Delete
  5. SELECT fnNumberToWords(888)
    showing error 'fnNumberToWords' is not a recognized built-in function name.
    phease help how call

    ReplyDelete
  6. i did this but i am getting conversion failed when converting the nvarchar to datatype int. i used cast also even though it is getting .plz help me, it is really urgent

    ReplyDelete

Recommended Post Slide Out For Blogger