Formatting a number with thousand separators

Category : Tips User Rating : 4 Stars      Views : 5 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.
Sometimes it can be useful to add thousand separators (commas) to large numbers to make them easier to view. There is no simple way to do this in SQL for an int and bigint, but it can be achieved by converting to a money type first. The solution below gets the desired result :
DECLARE @BigNumber BIGINT
SET @BigNumber = 1234567891234

SELECT REPLACE(CONVERT(VARCHAR,CONVERT(MONEY,@BigNumber),1), '.00','')
When run in Management Studio the result is :
1,234,567,891,234
This works by using the CONVERT function to convert the number from MONEY to a VARCHAR with a style of 1, which adds the commas and also a decimal point and two decimal places. As we don't really want the decimal places I've used the REPLACE function to strip them off.

You might prefer to encapsulate this into a User Defined Function (UDF), though if performance is an issue a CLR should perform better.

I will finish by pointing out that formatting numbers is something that should really be left to the presentation layer (e.g. Reporting Services or Excel) rather than SQL, which is probably why Microsoft hasn’t included the functionality to do this. Nevertheless there are times when it is convenient to present large numbers in this way within SQL Server.

Related Articles

The following articles may also be of interest :

Link back to this article : https://www.sqlmatters.com/Articles/Formatting a number with thousand separators.aspx

Keywords

TSQL,number,format


Comments
Post by Greg on Thu 14 Mar 2013 20:18. Report Inappropriate Post

This won't work for a truly big BIGINT. Think max-possible, minus the least 4 digits; anything larger can't convert to a MONEY type.

Tip: convert the entire number to TWO strings. The right-most part should be a multiple-of-three in length (e.g. RIGHT(@blah,9)).

You can then dance those strings to MONEY and back again (and still, yes, replace the ".00") to get the commas. But mind the possibility that the "right-hand" string may end up including leading zeros, which have to be kept in this case, when the two comma-laden strings are sewn back together. My way to handle this was to flag it; if the right-hand piece starts with a zero I can replace that zero with a 9 before converting the string to money, and back to a zero when it comes back to a varchar.

I suppose in theory this could work for a number with any number of digits, though then you'll need recursion or windowing or something to break the entire number up into an arbitrary number of substrings (each possibly with leading zeros that must be kept). I don't myself have a need for this so I haven't bothered to try. But I did need to be able to convert very-large BIGINTs; two substrings is plenty for that.
Post by Kashif on Wed 19 Nov 2014 09:57. Report Inappropriate Post

Money data type is not working with 18 digits number for e.g: "9000000000000000.000000". Please suggest some alternative solution with example.
Thanks in Advance.
Post by Loyd on Thu 03 Sep 2015 17:37. Report Inappropriate Post

This one always ticks me off. As a DBA, the T-SQL prompt IS often my "presentation layer". I am often running text based reports for my own consumption, on table sizes and row counts. Why should I have to encase simple T-SQL inside a programming language just to make it easier to tell quickly if a number is in the billions or one hundred millions?

Oracle SQL Plus makes this EXTREMELY easy with format number as 999,999,999,999. Once again, SQL Server developers assume that the only users of data are dot net developers, not including the DBAs who take care of all this data and only want/need a simple T-SQL output 90% of the time.
Post by B_Nana on Tue 17 May 2016 16:28. Report Inappropriate Post

I need to create this format below
0001 I dont know what to I'm using SQL 2008
Post by Roger on Thu 16 Nov 2017 04:41. Report Inappropriate Post

DECLARE @Amount DEC(18,2)
SET @Amount = 1025040.23
select
[raw] = str(@Amount,18,2)
,[Standard] = cast(format(@Amount, 'N', 'en-US') as varchar(20))
,[European] = cast(format(@Amount, 'N', 'de-de') as varchar(20))

Result:
raw Standard European
1025040.23 1,025,040.23 1.025.040,23
Post by bossman on Tue 05 Dec 2017 22:49. Report Inappropriate Post

You can also make custom format strings for the format function

https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-numeric-format-strings#the--custom-specifier-2
Post by Ivana on Tue 05 Jun 2018 15:29. Report Inappropriate Post

How do I save a number like 180.000.000,00 ? Do I just put in the money data type or do the formatting like you did?
Post by Max on Sat 06 Oct 2018 01:39. Report Inappropriate Post

SELECT FORMAT(1234567891234, '#,#')

Result:
1,234,567,891,234
Post by Darren on Mon 03 Dec 2018 16:19. Report Inappropriate Post

Another way to get commas, which is easier (for me) to remember:

SELECT FORMAT(123456789, 'N0') --that's a zero, specifying the number of decimal places

Result:
123,456,789
Post by Roderick Gors on Thu 26 Sep 2019 09:04. Report Inappropriate Post
Website : https://www.cobra-ts.eu/fr/
Man, thank you, you saved my day!!
Post by Dileep Konanki on Mon 30 Mar 2020 09:04. Report Inappropriate Post

SELECT FORMAT(123456789, 'N0') --that's a zero, specifying the number of decimal places
This is awesome ..
Post by Amirhossein on Sat 22 Aug 2020 05:38. Report Inappropriate Post

FORMAT(123456789, 'N0') it's very good but not work when data is 0 like FORMAT(0, 'N0') result is nothing '' !!!!!!!!
Post by amirhossein on Sat 22 Aug 2020 05:43. Report Inappropriate Post

I use SELECT FORMAT(1234567891234, '#,#') its very good idea but not work when number is 0 , 0.0 or any type of zero result is nothing , in FORMAT(123456789, 'N0') is not work well when you need result view 136,524.2 , 12 , 0 , 25.2 .

Post a comment   No login required !

Name : Email : Website :
Will be displayed alongside your comment
 Not displayed Optional, but displayed if entered