Convert number in Indian currency format in SQL Server

Moni
 
on Feb 15, 2018 04:11 AM
8755 Views

string format for indian rupee in sql like #,##,###

Download FREE API for Word, Excel and PDF in ASP.Net: Download
nagaraju60
 
on Feb 15, 2018 04:37 AM

Dear @moni,

Please try below query, it might be help you

 

DECLARE @BigNumber BIGINT
SET @BigNumber = 1234567891234

SELECT REPLACE(CONVERT(VARCHAR,CONVERT(MONEY,@BigNumber),1), '.00','')

Result : 1,234,567,891,234

Please refer below article,

https://www.sqlmatters.com/Articles/Formatting%20a%20number%20with%20thousand%20separators.aspx

https://www.sqlservercentral.com/Forums/Topic1145424-1292-1.aspx

http://www.aspforums.net/Threads/150473/Format-number-and-add-Comma-after-two-digits-in-Currency-using-C-Net/

 

nagaraju60
 
on Feb 15, 2018 07:56 AM

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

 

dharmendr
 
on Feb 16, 2018 01:07 AM
on Feb 19, 2018 03:02 AM

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

      Amount
1,11,65,48,238