Convert number in Indian currency format in SQL Server

Dear @Moni,

         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.

Please refer below article,

http://sqlserver20.blogspot.in/2012/05/convert-number-into-indian-currency.html

Hi Moni,

Check the test query.

SQL

DECLARE @InNumericValue NUMERIC(38,2)
SET @InNumericValue = 1116548238.53
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)

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

SELECT @StrFinal = ISNULL(@StrFinal,00)
SELECT @StrFinal 'Amount'

Output