Tuesday 22 May 2012

Convert Number in Indian Currency Format


    In SQL Server 2005, 2008 or 2012, it is simple to convert a number into Western Currency Format, but if to convert it into Indian Currency Format, there is no direct Inbuilt Function available in MS SQL. To convert it into Indian Format you need to write an SQL script. And to Convert in Hindi words, follow the link here.. . And if you wish to Convert in English words,then follow the link here..
   
   Below is the MS SQL User Defined Function which converts Number into Western Currency Format and in Indian Currency Format as well : 


CREATE FUNCTION [dbo].[udf_NumberToCurrency]
(    
  @InNumericValue NUMERIC(38,2)
 ,@InFormatType   VARCHAR(10)
)
RETURNS VARCHAR(60)
AS
BEGIN
DECLARE     @RetVal     VARCHAR(60)
            ,@StrRight  VARCHAR(5) 
            ,@StrFinal  VARCHAR(60)
            ,@StrLength INT
                 
SET @RetVal = ''
SET @RetVal= @InNumericValue 
SET @RetVal= SUBSTRING(@RetVal,1,CASE WHEN CHARINDEX('.', @RetVal)=0 THEN LEN(@RetVal)ELSE CHARINDEX('.',@RetVal)-1 END)

IF(@InFormatType = 'US')
BEGIN
SET @StrFinal= CONVERT(VARCHAR(60), CONVERT(MONEY, @RetVal) , 1)
SET @StrFinal= SUBSTRING(@StrFinal,0,CHARINDEX('.', @StrFinal))
END

ELSE
IF(@InFormatType = 'IND')
BEGIN
SET @StrLength = LEN(@RetVal)
IF(@StrLength > 3)
BEGIN
      SET @StrFinal = RIGHT(@RetVal,3)         
      SET @RetVal = SUBSTRING(@RetVal,-2,@StrLength)
      SET @StrLength = LEN(@RetVal)
      IF (LEN(@RetVal) > 0 AND LEN(@RetVal) < 3)
            BEGIN
            SET @StrFinal = @RetVal + ',' + @StrFinal
            END
      WHILE LEN(@RetVal) > 2
            BEGIN
            SET @StrRight=RIGHT(@RetVal,2)               
            SET @StrFinal = @StrRight + ',' + @StrFinal
            SET @RetVal = SUBSTRING(@RetVal,-1,@StrLength)
            SET @StrLength = LEN(@RetVal)
            IF(LEN(@RetVal) > 2)
            CONTINUE
            ELSE
            SET @StrFinal = @RetVal + ',' + @StrFinal
            BREAK
            END
      END
      ELSE
      BEGIN
            SET @StrFinal = @RetVal
      END
   
END
SELECT @StrFinal = ISNULL(@StrFinal,00)
RETURN @StrFinal
END


    Above function takes two parameters as input, firstone is the number you want to format and second one is the format type ('US' for Western and 'IND' for Indian Formats). and in return give a formatted string.

e.g.
 SELECT dbo.udf_NumberToCurrency (1116548238.53,'US') AS [Amount]
 
  --Output :
  [Amount]
  1,116,548,238
 
 SELECT dbo.udf_NumberToCurrency (1116548238.53,'IND') AS [Amount]
   
   --Output :
   [Amount]
  1,11,65,48,238



This is to be noted here that, the decimal points will be omitted in both formats. But if you like to have the decimal points as well, then make a bit change in the above function as below :

CREATE FUNCTION [dbo].[udf_NumberToCurrency]
(    
  @InNumericValue NUMERIC(38,2)
 ,@InFormatType   VARCHAR(10)
)
RETURNS VARCHAR(60)
AS
BEGIN
DECLARE     @RetVal     VARCHAR(60)
            ,@StrRight  VARCHAR(5) 
            ,@StrFinal  VARCHAR(60)
            ,@StrLength INT
                 
SET @RetVal = ''
SET @RetVal= @InNumericValue 

IF(@InFormatType = 'US')
BEGIN
SET @StrFinal= CONVERT(VARCHAR(60), CONVERT(MONEY, @RetVal) , 1)
END

ELSE
IF(@InFormatType = 'IND')
BEGIN
SET @StrLength = LEN(@RetVal)
IF(@StrLength > 6)
BEGIN
      SET @StrFinal = RIGHT(@RetVal,6)         
      SET @RetVal = SUBSTRING(@RetVal,-5,@StrLength)
      SET @StrLength = LEN(@RetVal)
      IF (LEN(@RetVal) > 0 AND LEN(@RetVal) < 3)
            BEGIN
            SET @StrFinal = @RetVal + ',' + @StrFinal
            END
      WHILE LEN(@RetVal) > 2
            BEGIN
            SET @StrRight=RIGHT(@RetVal,2)               
            SET @StrFinal = @StrRight + ',' + @StrFinal
            SET @RetVal = SUBSTRING(@RetVal,-1,@StrLength)
            SET @StrLength = LEN(@RetVal)
            IF(LEN(@RetVal) > 2)
            CONTINUE
            ELSE
            SET @StrFinal = @RetVal + ',' + @StrFinal
            BREAK
            END
      END
      ELSE
      BEGIN
            SET @StrFinal = @RetVal
      END
   
END
SELECT @StrFinal = ISNULL(@StrFinal,00)
RETURN @StrFinal
END


And now try for your inputs :

 SELECT dbo.udf_NumberToCurrency (1116548238.53,'US') AS [Amount]
 
  --Output :
  [Amount]
  1,116,548,238.53
 
 SELECT dbo.udf_NumberToCurrency (1116548238.53,'IND') AS [Amount]
   
   --Output :
   [Amount]
  1,11,65,48,238.53




Reference: Govind Badkur(http://sqlserver20.blogspot.com)

21 comments:

  1. Thank you very much this blog was very helpful...for me.

    ReplyDelete
  2. How to allow 4 decimal point

    ReplyDelete
  3. very help full.. appreciated!!!

    ReplyDelete
  4. while entering -ve amount it is not working

    ReplyDelete
  5. I recently found many useful information in your website especially this blog page. Among the lots of comments on your articles. Thanks for sharing. convert money

    ReplyDelete