CREATE FUNCTION dbo.com_FormatNumber(@value BIGINT) RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @minus CHAR, @working VARCHAR(MAX), @result VARCHAR(MAX), @section VARCHAR(4)
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 = ''
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
IF (@minus <> '')
RETURN @minus + @working + @result
RETURN @working + @result
END
Usage is
PRINT dbo.FormatNumber(123456)
PRINT dbo.FormatNumber(-123)
The result will be
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.