SQL Function that convert Numerical Currency to String Format

In this blog I am going to explain how to convert Numerical Currency to String Format using sql function.

SQL Function that convert numerical currency to String format. 

Ex. If you pass the

SELECT dbo.fn_Convert_NumToWords(1268.00, 'Y')
1268 then it return One Thousand Two Hundred Sixty Eight Rupees.SELECT dbo.fn_Convert_NumToWords(1268.50, 'Y')
1268.50 then it return One Thousand Two Hundred Sixty Eight Rupees and Fifty Paise
If you want only round fig. in calculation then
SELECT dbo.fn_Convert_NumToWords(1268.50, 'N')
1268.50 than it return One Thousand Two Hundred Sixty Eight Rupees. 
/****** Object:  UserDefinedFunction [dbo].[fn_Convert_NumToWords]    Script Date: 07/23/2012 14:37:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
-- SELECT dbo.fn_Convert_NumToWords(1268.00, 'Y')
CREATE FUNCTION [dbo].[fn_Convert_NumToWords]
(
      @Number  NUMERIC(18, 2),
      @CPaise  CHAR(1)
)
RETURNS VARCHAR(2000)
AS
BEGIN
      DECLARE @StrNumber    VARCHAR(10),
              @SLacs        CHAR(2),
              @SThou        CHAR(2),
              @SHun         CHAR(2)
     
      DECLARE @STenUnt      CHAR(2),
              @STen         CHAR(2),
              @SUnt         CHAR(2),
              @SDecimal     CHAR(2)
     
      DECLARE @ILacs        INT,
              @IThou        INT,
              @IHun         INT,
              @ITenUnt      INT,
              @ITen         INT,
              @IUnt         INT,
              @IDecimal     INT
     
      DECLARE @SNumToWords  VARCHAR(100),
              @numberInWord       VARCHAR(10)
     
      SELECT @StrNumber = REPLICATE('0', 10 - LEN(LTRIM(RTRIM(CONVERT(VARCHAR, @Number)))))
             + LTRIM(RTRIM(CONVERT(VARCHAR, @Number)))
     
      SELECT @SNumToWords = ''
      IF LEN(LTRIM(RTRIM(CONVERT(VARCHAR, @Number)))) > 4
      BEGIN
          SELECT @SLacs = SUBSTRING(@StrNumber, 1, 2)
          SELECT @ILacs = CONVERT(INT, @SLacs)       
          IF @ILacs > 0
          BEGIN
              SELECT @STen = SUBSTRING(@StrNumber, 1, 1)         
              SELECT @SUnt = SUBSTRING(@StrNumber, 2, 1)
              IF CONVERT(INT, @STen) = 1
              BEGIN
                  SELECT @ITen = CONVERT(INT, SUBSTRING(@StrNumber, 1, 2))     
                  SELECT @IUnt = 0
              END
              ELSE
              BEGIN
                  SELECT @ITen = CONVERT(INT, @STen) * 10                    
                  SELECT @IUnt = CONVERT(INT, @SUnt)
              END            
              IF @ITen > 0
              BEGIN
                  SELECT @numberInWord = ''    
                  SELECT @numberInWord = WordToNum_InWords
                  FROM   dbo.Gen_NumberInWords_Master
                  WHERE  WordToNum_Number = @ITen
                 
                  SELECT @SNumToWords = @SNumToWords + SPACE(1) + @numberInWord
              END
             
              IF @IUnt > 0
              BEGIN
                  SELECT @numberInWord = ''    
                  SELECT @numberInWord = WordToNum_InWords
                  FROM   Gen_NumberInWords_Master
                  WHERE  WordToNum_Number = @IUnt
                 
                  SELECT @SNumToWords = @SNumToWords + SPACE(1) + @numberInWord
              END             
              SELECT @SNumToWords = @SNumToWords + ' Lacs'
          END
         
          SELECT @SThou = SUBSTRING(@StrNumber, 3, 2)
         
          SELECT @IThou = CONVERT(INT, @SThou)
          IF @IThou > 0
          BEGIN
              SELECT @STen = SUBSTRING(@StrNumber, 3, 1)
              SELECT @SUnt = SUBSTRING(@StrNumber, 4, 1)   
              IF CONVERT(INT, @STen) = 1
              BEGIN
                  SELECT @ITen = CONVERT(INT, SUBSTRING(@StrNumber, 3, 2))
                  SELECT @IUnt = 0
              END
              ELSE
              BEGIN
                  SELECT @ITen = CONVERT(INT, @STen) * 10                    
                  SELECT @IUnt = CONVERT(INT, @SUnt)
              END                              
              IF @ITen > 0
              BEGIN
                  SELECT @numberInWord = ''    
                  SELECT @numberInWord = WordToNum_InWords
                  FROM   Gen_NumberInWords_Master
                  WHERE  WordToNum_Number = @ITen
                 
                  SELECT @SNumToWords = @SNumToWords + SPACE(1) + @numberInWord
              END
             
              IF @IUnt > 0
              BEGIN
                  SELECT @numberInWord = ''                      
                  SELECT @numberInWord = WordToNum_InWords
                  FROM   Gen_NumberInWords_Master
                  WHERE  WordToNum_Number = @IUnt
                 
                  SELECT @SNumToWords = @SNumToWords + SPACE(1) + @numberInWord
              END
             
              SELECT @SNumToWords = @SNumToWords + ' Thousand '
          END
         
          SELECT @SHun = SUBSTRING(@StrNumber, 5, 1)    
          SELECT @IHun = CONVERT(INT, @SHun)         
          IF @IHun > 0
          BEGIN
              SELECT @numberInWord = ''              
              SELECT @numberInWord = WordToNum_InWords
              FROM   Gen_NumberInWords_Master
              WHERE  WordToNum_Number = @IHun
             
              SELECT @SNumToWords = @SNumToWords + @numberInWord + ' Hundred'
          END
         
          SELECT @STenUnt = SUBSTRING(@StrNumber, 6, 2)
         
          SELECT @ITenUnt = CONVERT(INT, @STenUnt)         
          IF @ITenUnt > 0
          BEGIN
              SELECT @STen = SUBSTRING(@StrNumber, 6, 1)               
              SELECT @SUnt = SUBSTRING(@StrNumber, 7, 1)               
              IF CONVERT(INT, @STen) = 1
              BEGIN
                  SELECT @ITen = CONVERT(INT, SUBSTRING(@StrNumber, 6, 2))                       
                  SELECT @IUnt = 0
              END
              ELSE
              BEGIN
                  SELECT @ITen = CONVERT(INT, @STen) * 10                    
                  SELECT @IUnt = CONVERT(INT, @SUnt)
              END                        
              IF @ITen > 0
              BEGIN
                  SELECT @numberInWord = ''    
                  SELECT @numberInWord = WordToNum_InWords
                  FROM   Gen_NumberInWords_Master
                  WHERE  WordToNum_Number = @ITen
                 
                  --Select @SNumToWords = @SNumToWords + Space(1) + @numberInWord
                  SELECT @SNumToWords = @SNumToWords + SPACE(1) + @numberInWord
              END
             
              IF @IUnt > 0
              BEGIN
                  SELECT @numberInWord = ''                      
                  SELECT @numberInWord = WordToNum_InWords
                  FROM   Gen_NumberInWords_Master
                  WHERE  WordToNum_Number = @IUnt
                 
                 
                  SELECT @SNumToWords = @SNumToWords + SPACE(1) + @numberInWord
              END
          END
         
          SELECT @SNumToWords = @SNumToWords + SPACE(1) + 'Rupees'
      END
      ELSE
      BEGIN
          SELECT @SLacs = SUBSTRING(LTRIM(RTRIM(CONVERT(VARCHAR, @Number))), 1, 1)
          SELECT @ILacs = CONVERT(INT, @SLacs)       
          IF @ILacs > 0
             AND @ILacs <> 1
          BEGIN
              SELECT @numberInWord = ''        
              SELECT @numberInWord = WordToNum_InWords
              FROM   Gen_NumberInWords_Master
              WHERE  WordToNum_Number = @ILacs
             
              SELECT @SNumToWords = @SNumToWords + SPACE(1) + @numberInWord + SPACE(1) +
                     'Rupees'
          END
          ELSE
          BEGIN
              SELECT @numberInWord = ''              
              SELECT @numberInWord = WordToNum_InWords
              FROM   Gen_NumberInWords_Master
              WHERE  WordToNum_Number = @ILacs
             
              SELECT @SNumToWords = @SNumToWords + @numberInWord + SPACE(1) + 'Rupee'
          END
      END  
      IF @CPaise = 'Y'
      BEGIN
          SELECT @SDecimal = SUBSTRING(@StrNumber, 9, 2)         
          SELECT @IDecimal = CONVERT(INT, @SDecimal)       
          IF @IDecimal > 0
          BEGIN
              SELECT @SNumToWords = @SNumToWords + ' and'              
              SELECT @STen = SUBSTRING(@SDecimal, 1, 1)                
              SELECT @SUnt = SUBSTRING(@SDecimal, 2, 1)                      
              IF CONVERT(INT, @STen) = 1
              BEGIN
                  SELECT @ITen = CONVERT(INT, SUBSTRING(@StrNumber, 9, 2))                       
                  SELECT @IUnt = 0
              END
              ELSE
              BEGIN
                  SELECT @ITen = CONVERT(INT, @STen) * 10                    
                  SELECT @IUnt = CONVERT(INT, @SUnt)
              END                              
              IF @ITen > 0
              BEGIN
                  SELECT @numberInWord = ''                      
                  SELECT @numberInWord = WordToNum_InWords
                  FROM   Gen_NumberInWords_Master
                  WHERE  WordToNum_Number    = @ITen
                 
                  SELECT @SNumToWords = @SNumToWords + SPACE(1) + @numberInWord
              END
             
              IF @IUnt > 0
              BEGIN
                  SELECT @numberInWord = ''          
                  SELECT @numberInWord = WordToNum_InWords
                  FROM   Gen_NumberInWords_Master
                  WHERE  WordToNum_Number = @IUnt
                 
                  SELECT @SNumToWords = @SNumToWords + SPACE(1) + @numberInWord
              END
             
              SELECT @SNumToWords = @SNumToWords + SPACE(1) + 'Paise'
          END
      END
            RETURN LTRIM(RTRIM(@SNumToWords))
END