Format a number (with commas) in SQL

CREATE FUNCTION dbo.com_FormatNumber(@value BIGINT) RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @minus CHAR, @working VARCHAR(MAX), @result VARCHAR(MAX), @section VARCHAR(4)
    -- First, handle the sign
    IF (@value < 0) SET @minus = '-' ELSE SET @minus = ''
    SET @working = CAST(@value AS VARCHAR)
    if (@minus <> '') SET @working = SUBSTRING(@working, 2, LEN(@working)-1)
    
    SET @result  = ''
    
	-- break apart the number into sections of 3 digits and insert commas
    WHILE LEN(@working) > 3
    BEGIN
        SET @section = ',' + SUBSTRING(@working, LEN(@working)-2, 3)
        SET @working = SUBSTRING(@working, 1, LEN(@working)-3)
        SET @result  = @section + @result
    END
    
	-- add the remaining and tack on that sign if needed
    IF (@minus <> '')
        RETURN @minus + @working + @result
        
    RETURN @working + @result
END

PRINT dbo.FormatNumber(123456)
PRINT dbo.FormatNumber(-123)

123,456
-123

Chris Maunder is the co-founder of CodeProject and ContentLab.com, and has been a prominent figure in the software development community for nearly 30 years. Hailing from Australia, Chris has a background in Mathematics, Astrophysics, Environmental Engineering and Defence Research. His programming endeavours span everything from FORTRAN on Super Computers, C++/MFC on Windows, through to to high-load .NET web applications and Python AI applications on everything from macOS to a Raspberry Pi. Chris is a full-stack developer who is as comfortable with SQL as he is with CSS.

In the late 1990s, he and his business partner David Cunningham recognized the need for a platform that would facilitate knowledge-sharing among developers, leading to the establishment of CodeProject.com in 1999. Chris's expertise in programming and his passion for fostering a collaborative environment have played a pivotal role in the success of CodeProject.com. Over the years, the website has grown into a vibrant community where programmers worldwide can connect, exchange ideas, and find solutions to coding challenges. Chris is a prolific contributor to the developer community through his articles and tutorials, and his latest passion project, CodeProject.AI.

In addition to his work with CodeProject.com, Chris co-founded ContentLab and DeveloperMedia, two projects focussed on helping companies make their Software Projects a success. Chris's roles included Product Development, Content Creation, Client Satisfaction and Systems Automation.