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