Display Number with Commas in SQL

-- Test this function : 
-- SELECT dbo.NumericToCurrency (1116548238,'US') AS RetValue
-- SELECT dbo.NumericToCurrency (10000,'IND') AS RetValue
-- For Indian Format - 'IND', FOR US Format - 'US'

CREATE FUNCTION [dbo].[NumericToCurrency]
( 
   @InNumericValue MONEY
  ,@InFormatType  VARCHAR(10)
)

RETURNS VARCHAR(50)

AS
BEGIN

 DECLARE   @RetVal  VARCHAR(50)
    ,@StrRight  VARCHAR(5) 
    ,@StrFinal  VARCHAR(50) 
    ,@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(50), 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

Reference: Muthukumar (http://nadarmuthukumar.blogspot.in/)

Posted in: SQL,SQL Interview