Here’s another scalar user-defined function (UDF) that I frequently reference. This may be referenced on occasion in subsequent posts on this blog.
USE Admin
GO
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
12/29/10 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
GO
No comments:
Post a Comment