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
