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