This is simple function which can format a given number as Comma delimited number.

ex : 98712345 as 98,712,345

Thanks to http://siccolo.blogspot.com/

(Attached is the original code which works on SQL Server 2000 and SQL Server 2005.)

(download) formatnumber

I have slightly modified it for SQLServer 2005.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
– =============================================
– Author:
– Create date:
– Description:
– =============================================
– Verify that stored procedure does not exist.
IF OBJECT_ID (‘fn_format_number’, ‘FN’ ) IS NOT NULL
    DROP FUNCTION fn_format_number

PRINT ‘Function fn_format_number is Deleted’
GO
CREATE FUNCTION fn_format_number (@int_value int ) returns varchar(20)
AS
BEGIN
 DECLARE @is_negative BIT
 SELECT @is_negative = CASE WHEN @int_value<0 THEN 1 ELSE 0 END

 IF @is_negative = 1
  SET @int_value = -1*@int_value

 DECLARE @return_value VARCHAR(20)
 SET @return_value = CONVERT(VARCHAR, ISNULL(@int_value, 0))

 DECLARE @before VARCHAR(20), @after VARCHAR(20)

 SET @before = @return_value
 SET @after=”

 – after every third character:
 DECLARE @i INT
 IF LEN(@before)>3
 BEGIN
  SET @i = 3
  WHILE @i>1 and @i < len(@before)
  BEGIN
   SET @before = substring(@before,1,len(@before)-@i) + ‘,’ + right(@before,@i)
   SET @i = @i + 4
  END
 END
 SET @return_value = @before + @after

 IF @is_negative = 1
  SET @return_value = ‘-’ + @return_value

 RETURN @return_value
END
GO
PRINT ‘Function fn_format_number is Created’
GO