This function simply formats an integer with commas to
separate thousands, if necessary.
This object is referenced by various other objects in this
blog, including view vwTable which will be presented soon.
USE Admin
IF
Object_ID('dbo.udfFormatInteger')
Is Not Null
DROP FUNCTION dbo.udfFormatInteger
go
CREATE FUNCTION dbo.udfFormatInteger
(@In bigint)
RETURNS varchar(20)
AS
/*
DATE AUTHOR REMARKS
6/22/13 PPaiva Initial creation.
DESCRIPTION
Formats a given integer with
commas.
Examples:
IN OUT
123 123
12345 12,345
-54321 -54,321
USAGE
SELECT
dbo.udfFormatInteger(1234567890)
SELECT dbo.udfFormatInteger(12345)
SELECT dbo.udfFormatInteger(-54321)
SELECT dbo.udfFormatInteger(0)
*/
BEGIN
DECLARE @Out varchar(20),
@sIn varchar(20),
@Balance varchar(20),
@CurrTextLen smallint,
@IsNegative
bit
IF @In < 0
BEGIN
SET @IsNegative
= 1
SET @In = Abs(@In)
END
SET @sIn = Convert(varchar, @In)
SET @CurrTextLen = Len(@sIn)
SET @Out = ''
SET @Balance = @sIn
IF @CurrTextLen > 3
BEGIN
WHILE 1 =
1
BEGIN
SET @Out = ',' + Right(@Balance, 3) + @Out
SET @Balance = Substring(@sIn, 1, @CurrTextLen - 3)
SET @CurrTextLen = Len(@Balance)
IF @CurrTextLen > 3
CONTINUE
ELSE
BEGIN
SET @Out = @Balance + @Out
BREAK
END
END
END
ELSE
SET @Out = @sIn
IF @IsNegative = 1
SET @Out = '-' + @Out
RETURN @Out
END