How To Convert Amount To Words In Sql Server

How to convert amount to words in sql server using scaler function.

In this article we learn how to convert amount to words using sql server scaler function. we create 2 functions for easy to understand and manage. In 1st function we are return only general words like (One, Two, Eleven ,Twelve etc.) & in 2nd function we create a logic for convert amount to words. lets start it.

First we create a new function GET_DIGIT2WORD() for get the general words just like below,

CREATE FUNCTION [dbo].[GET_DIGIT2WORD] (
  @M_AMOUNT AS NUMERIC(18,0)
) RETURNS VARCHAR(100)
BEGIN
  DECLARE @M_FIRST AS VARCHAR(50)
  DECLARE @M_LAST AS VARCHAR(50)
  
  SELECT @M_FIRST = 
    CASE WHEN @M_AMOUNT >= 10 AND @M_AMOUNT <= 19 THEN
      CASE @M_AMOUNT 
        WHEN 10 THEN 'TEN'
        WHEN 11 THEN 'ELEVEN'
        WHEN 12 THEN 'TWELVE'
        WHEN 13 THEN 'THIRTEEN'
        WHEN 14 THEN 'FOURTEEN'
        WHEN 15 THEN 'FIFTEEN'
        WHEN 16 THEN 'SIXTEEN'
        WHEN 17 THEN 'SEVENTEEN'
        WHEN 18 THEN 'EIGHTEEN'
        WHEN 19 THEN 'NINETEEN'
        ELSE '' 
      END
    ELSE
      CASE WHEN LEN(@M_AMOUNT) = 2 THEN
        CASE LEFT(@M_AMOUNT,1) 
          WHEN  2 THEN 'TWENTY '
          WHEN  3 THEN 'THIRTY '
          WHEN  4 THEN 'FORTY '
          WHEN  5 THEN 'FIFTY '
          WHEN  6 THEN 'SIXTY '
          WHEN  7 THEN 'SEVENTY '
          WHEN  8 THEN 'EIGHTY '
          WHEN  9 THEN 'NINETY '
          ELSE ''
        END
      ELSE
        ''
      END
    END

  SELECT @M_LAST = 
  CASE WHEN @M_AMOUNT >= 10 AND @M_AMOUNT <= 19 THEN
    ''
  ELSE
    CASE RIGHT(@M_AMOUNT,1) 
      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'
      ELSE ''
    END
  END

  RETURN RTRIM(LTRIM(@M_FIRST + @M_LAST))
END
GO

Now we create another function GET_NUM2WORD() for display amount to words just like below,

CREATE FUNCTION [dbo].[GET_NUM2WORD] (
  @M_AMOUNT AS NUMERIC(18,2)
) RETURNS VARCHAR(MAX)
BEGIN
  DECLARE @M_RS AS NUMERIC(18,0)
  DECLARE @M_PAISA AS NUMERIC(18,0)
  DECLARE @M_CURRENT AS NUMERIC(18,0)
  DECLARE @M_RETSTR AS VARCHAR(MAX)
  DECLARE @M_POSTFIX AS VARCHAR(20)  
  DECLARE @M_DIGIWORD AS VARCHAR(100)

  SET @M_AMOUNT = ISNULL(@M_AMOUNT,0)
  SET @M_RS = LEFT(@M_AMOUNT,LEN(@M_AMOUNT)-3)
  SET @M_PAISA = RIGHT(@M_AMOUNT,2)
  SET @M_RETSTR = ''

  IF @M_RS = 0 AND @M_PAISA = 0 
  BEGIN
    SET @M_RETSTR = 'ZERO'
  END
  ELSE
  BEGIN
    IF @M_RS = 0
    BEGIN
      SET @M_RETSTR = 'ZERO'
    END
    ELSE
    BEGIN
      WHILE @M_RS > 0
      BEGIN
        SELECT @M_CURRENT = 
          CASE WHEN LEN(@M_RS) = 3 THEN LEFT(@M_RS,1)
          WHEN LEN(@M_RS) <= 2 THEN LEFT(@M_RS,2)
          ELSE CASE WHEN (LEN(@M_RS)-2) % 2 = 0 THEN LEFT(@M_RS,1) ELSE LEFT(@M_RS,2) END
          END
        
        SELECT @M_DIGIWORD = [dbo].[GET_DIGIT2WORD](@M_CURRENT)

        SELECT @M_POSTFIX = CASE 
        WHEN LEN(@M_RS) = 8 OR LEN(@M_RS) = 9 THEN 'CRORE' 
        WHEN LEN(@M_RS) = 6 OR LEN(@M_RS) = 7 THEN 'LAKH' 
        WHEN LEN(@M_RS) = 4 OR LEN(@M_RS) = 5 THEN 'THOUSAND'  
        WHEN LEN(@M_RS) = 3 THEN 'HUNDRED'
        ELSE '' END
        
        SELECT @M_RS = CASE WHEN LEN(@M_RS) > 2 THEN RIGHT(@M_RS,LEN(@M_RS)-LEN(@M_CURRENT)) ELSE 0 END

        SET @M_RETSTR = @M_RETSTR + ' ' + @M_DIGIWORD + ' ' + @M_POSTFIX
      END
    END

    IF @M_PAISA = 0 
    BEGIN
      SET @M_RETSTR = @M_RETSTR + ' RUPEES'
    END
    ELSE
    BEGIN
      SELECT @M_DIGIWORD = [dbo].[GET_DIGIT2WORD](@M_PAISA)
      SET @M_RETSTR = @M_RETSTR + ' RUPEES AND ' + @M_DIGIWORD + ' PAISA'
    END
  END
    
  RETURN RTRIM(LTRIM(@M_RETSTR))
END
GO

Its Done, you just execute function like below & got the amount to words.

You can also got the result with passing Table column to GET_NUM2WORD() as simple as that.

I hope you enjoy & Thank you for reading this article.