SQL Server function code for formatting numbers to have Comma seperator with Out any Loop

Hi,


SQL Server does't have any inbuild function to put thousand Comma sepeator. You can use the Below MS SQL server user function or Code for formatting Numeric Values to have Comma seperator.

This Approach is an optimal one and dont use any Loop.

View Alternate Approach 


CREATEFUNCTION [fn_CommaSeperateNumeral]

(@completeValue NVARCHAR(200)

)
RETURNSNVARCHAR(200)
AS

BEGIN
DECLARE @intValue NVARCHAR(50), @precValue NVARCHAR(50)

SELECT @intValue = SUBSTRING(CONVERT(NVARCHAR,CONVERT(MONEY,@completeValue),1), 1, CHARINDEX('.',CONVERT(NVARCHAR,CONVERT(MONEY,@completeValue),1),1)-1)

IF CHARINDEX('.',@completeValue,1) >0

SELECT @precValue = SUBSTRING(@completeValue, CHARINDEX('.',@completeValue,1)+1,LEN(@completeValue))

SELECT @completeValue = @intValue

IF @precValue IS NOT NULL

SELECT @completeValue = @completeValue + '.' + ISNULL(@precValue,'')

RETURN @completeValue

END