About Me

Colorado
Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group for 11 years, from January 2009 to 2020.

Saturday, June 22, 2013

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






No comments:

Post a Comment