How to Convert currency to words in SQL Server

Other SQL Server Topics
1

Hi experts, I have created scalar-function for converting currency to words in SQL Server. However, I couldn't get the desired result and I'm not sure where is the problem. The problems occur when converting to million and billion .

CREATE FUNCTION [dbo].[func_number_to_word_format] 
(@AMOUNT DECIMAL(18,2)
)
RETURNS NVARCHAR(max)
AS
BEGIN
    DECLARE @M_RM AS DECIMAL(18,0)
    DECLARE @M_CENT AS DECIMAL(18,0)
    DECLARE @M_CURRENT AS DECIMAL(18,0)
    DECLARE @M_RETSTR AS NVARCHAR(MAX)
    DECLARE @M_POSTFIX AS NVARCHAR(20)    
    DECLARE @M_DIGIT2WORD AS NVARCHAR(100)
    
    SET @AMOUNT = ISNULL(@AMOUNT,0)
    SET @M_RM = LEFT(@AMOUNT,LEN(@AMOUNT)-3)
    SET @M_CENT = RIGHT(@AMOUNT,2)
    SET @M_RETSTR = ''

  IF @M_RM = 0 AND @M_CENT = 0 
  BEGIN
    SET @M_RETSTR = 'RINGGIT MALAYSIA ZERO ONLY'
  END
  ELSE
  IF @M_RM = 0 AND @M_CENT > 0 
  BEGIN
    SELECT @M_DIGIT2WORD = [dbo].[func_digit_to_word_format](@M_CENT)
    SET @M_RETSTR = 'RINGGIT MALAYSIA ' + @M_DIGIT2WORD + ' CENTS ONLY'
  END
  ELSE
  BEGIN
    IF @M_RM = 0
    BEGIN
      SET @M_RETSTR = ' ZERO '
    END
    ELSE
    BEGIN
      WHILE @M_RM > 0
      BEGIN
        SELECT @M_CURRENT = 
          CASE WHEN LEN(@M_RM) = 3 OR LEN(@M_RM) = 7 THEN LEFT(@M_RM,1) 
          WHEN LEN(@M_RM) <= 2 THEN LEFT(@M_RM,2) 
          ELSE 
               CASE WHEN (LEN(@M_RM)-2) % 2 = 0 THEN LEFT(@M_RM,1) ELSE LEFT(@M_RM,2) END 
          END
    SELECT @M_DIGIT2WORD = [dbo].[func_digit_to_word_format](@M_CURRENT)
        SELECT @M_POSTFIX = CASE WHEN LEN(@M_RM) = 10 THEN 'BILLION ' 
                                 WHEN LEN(@M_RM) = 9 THEN 'HUNDRED MILLION ' 
                                 WHEN LEN(@M_RM) = 7 OR LEN(@M_RM) = 8 THEN 'MILLION ' 
                             WHEN LEN(@M_RM) = 6 THEN 
                        CASE WHEN RIGHT(@M_RM,5) >= 01111 THEN'HUNDRED '
                     WHEN RIGHT(@M_RM,5) = 00000 THEN'HUNDRED THOUSAND '
                     ELSE ' '
                     END
                                 WHEN LEN(@M_RM) = 4 OR LEN(@M_RM) = 5 THEN 'THOUSAND '  
                                 WHEN LEN(@M_RM) = 3 THEN 'HUNDRED '
                                 ELSE '' END
        SELECT @M_RM = CASE WHEN LEN(@M_RM) > 2 THEN RIGHT(@M_RM,LEN(@M_RM)-LEN(@M_CURRENT)) ELSE 0 END
        SET @M_RETSTR = @M_RETSTR + ' ' + @M_DIGIT2WORD + ' ' + @M_POSTFIX
      END
    END
    IF @M_CENT = 0 
    BEGIN
      SET @M_RETSTR ='RINGGIT MALAYSIA' + @M_RETSTR + 'ONLY'
    END
    ELSE
    BEGIN
      SELECT @M_DIGIT2WORD = [dbo].[func_digit_to_word_format](@M_CENT)
      SET @M_RETSTR = 'RINGGIT MALAYSIA' + @M_RETSTR + 'AND CENTS ' + @M_DIGIT2WORD + ' ONLY'
    END
  END
    
  RETURN RTRIM(LTRIM(@M_RETSTR))
END

 CREATE FUNCTION [dbo].[func_digit_to_word_format] 
    (@AMOUNT DECIMAL(18,0)
    )
    RETURNS NVARCHAR(max)
    AS
    BEGIN
        DECLARE @M_FIRST AS NVARCHAR(50)
      DECLARE @M_LAST AS NVARCHAR(50)   
     SELECT @M_FIRST = 
    CASE WHEN @AMOUNT >= 10 AND @AMOUNT <= 19 THEN
      CASE @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(@AMOUNT) = 2 THEN
        CASE LEFT(@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 @AMOUNT >= 10 AND @AMOUNT <= 19 THEN
    ''
  ELSE
    CASE RIGHT(@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

1. SELECT DBO.func_number_to_word_format(1234567891.23) AS WORDS
2. SELECT DBO.func_number_to_word_format(125461789.99) AS WORDS
3. SELECT DBO.func_number_to_word_format(101000.00) AS WORDS
4. SELECT DBO.func_number_to_word_format(15234567891.23) AS WORDS

Current Result:

  1. RINGGIT MALAYSIA ONE BILLION TWENTY THREE HUNDRED MILLION FOUR MILLION FIVE HUNDRED SIXTY SEVEN THOUSAND EIGHT HUNDRED NINETY ONE AND CENTS TWENTY THREE ONLY --Twenty three hundred million four million should be Two Hundred Thirty Four Million
  2. RINGGIT MALAYSIA TWELVE HUNDRED MILLION FIVE MILLION FOUR HUNDRED SIXTY ONE THOUSAND SEVEN HUNDRED EIGHTY NINE AND CENTS NINETY NINE ONLY -- Twelve Hundred Million Five Million should be One Hundred Twenty Five Million
  3. RINGGIT MALAYSIA ONE ONE THOUSAND ONLY -- Hundred words missing
  4. RINGGIT MALAYSIA FIFTEEN TWENTY THREE HUNDRED MILLION FOUR MILLION FIVE HUNDRED SIXTY SEVEN THOUSAND EIGHT HUNDRED NINETY ONE AND CENTS TWENTY THREE ONLY -- Billion words missing and Twenty Three Hundred Million Four Million should be Two Hundred Thirty Four Million

Final Result that expected:

  1. RINGGIT MALAYSIA ONE BILLION TWO HUNDRED THIRTY FOUR MILLION FIVE HUNDRED SIXTY SEVEN THOUSAND EIGHT HUNDRED NINETY ONE AND CENTS TWENTY THREE ONLY
  2. RINGGIT MALAYSIA ONE HUNDRED TWENTY FIVE MILLION FOUR HUNDRED SIXTY ONE THOUSAND SEVEN HUNDRED EIGHTY NINE AND CENTS NINETY NINE ONLY
  3. RINGGIT MALAYSIA ONE HUNDRED ONE THOUSAND ONLY
  4. RINGGIT MALAYSIA FIFTEEN BILLION TWO HUNDRED THIRTY FOUR MILLION FIVE HUNDRED SIXTY SEVEN THOUSAND EIGHT HUNDRED NINETY ONE AND CENTS TWENTY THREE ONLY
2

Hello,

I'm not going to debug your logic for you but I can suggest a way to proceed that is efficient. Make a test procedure for each of your functions, starting with the lowest level, which in this case would seem to be func_digit_to_word_format. Call it with enough variations of input to cover every line of it. Once you've verified that the output of that test is 100% correct, proceed upward and write a test procedure for the next higher level and do the same for it. Save the test procedures in case changes are needed to the functions later, so you can modify the test procedures to match and thereby verify correctness again. Best of luck.