Insert Commas Into Number String

,

This function will add commas to a given number passed as a string, separating thousands, millions, etc. Rather than doing long division to figure out how many "digit groups" there are, it simply reverses the number string, adds the commas after every third digit (after the decimal place, if present), and then re-reverses the string for return.

Examples:

PRINT dbo.fn_AddCommasToNumberString('20123.12') -- 20,123.12
PRINT dbo.fn_AddCommasToNumberString('2012312')  -- 2,012,312
PRINT dbo.fn_AddCommasToNumberString('23')       -- 23

Written by Jesse McLain

jesse@jessemclain.com

www.jessemclain.com

http://jessesql.blogspot.com

/*************************************************************************************************
**        File: fn_AddCommasToNumberString.sql
**        Desc: This function will add commas to a given number passed as a string.
** 
**                Examples:
**                ---------
**                PRINT dbo.fn_AddCommasToNumberString('20123.12')    -- 20,123.12
**                PRINT dbo.fn_AddCommasToNumberString('2012312')        -- 2,012,312
**                PRINT dbo.fn_AddCommasToNumberString('23')            -- 23
** 
**        Return values: varchar(max)
** 
**        Called by: 
** 
**        Parameters:
**        Input
**        ----------
**        @InputNumberStr varchar(max)
**
**        Output
**        -----------
**        none
**
**        Auth: Jesse McLain
**        Email: mailto:jesse@jessemclain.com
**        Web: http://www.jessemclain.com
**        Blog: http://www.jessesql.blogspot.com
**
**        Date: 03/30/2009
**
***************************************************************************************************
**        Change History
***************************************************************************************************
**        Date:        Author:                Description:
**        --------    --------            -------------------------------------------
**        20090330    Jesse McLain        Created script
***************************************************************************************************
**        ToDo List
***************************************************************************************************
**        Date:        Author:                Description:
**        --------    --------            -------------------------------------------
**        20090330    Jesse McLain        Created script
***************************************************************************************************
**        Notes
***************************************************************************************************
**        3/30/09 - This function works by taking the input number (as a string), reversing it, and 
**        then adding in commas after every third digit. When it reaches the end, it re-reverses the
**        result and returns it. It also accounts for any decimal place digits by use of @Offset - 
**        this variable stores this info by finding the position of the decimal place after string
**        reversal. After counting off 3 non-decimal place digits, the function stuffs a comma into
**        the reversed string, and increment @Offset and @CharIdx, because we are increasing the size
**        of the string by one char. 
**        
**************************************************************************************************/CREATE FUNCTION dbo.fn_AddCommasToNumberString
    (@InputNumberStr varchar(max))
RETURNS varchar(max)
AS
BEGIN
    DECLARE @OutputStr varchar(max)
    DECLARE @CharIdx int
    DECLARE @OffSet int

    SET @OutputStr = REVERSE(@InputNumberStr)

    SET @CharIdx = CHARINDEX('.', @OutputStr)
    SET @Offset = @CharIdx
    SET @CharIdx = CASE WHEN @CharIdx = 0 THEN 1 ELSE @CharIdx END

    WHILE @CharIdx <= LEN(@OutputStr)
    BEGIN
        IF @CharIdx - @Offset > 3 AND (@CharIdx - @Offset - 1) % 3 = 0
        BEGIN
            SET @OutputStr = STUFF(@OutputStr, @CharIdx, 0, ',')
            SET @Offset = @Offset + 1
            SET @CharIdx = @CharIdx + 1
        END

        SET @CharIdx = @CharIdx + 1
    END

    RETURN REVERSE(@OutputStr)
END

Rate

3.63 (8)

Share

Share

Rate

3.63 (8)