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)