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. Never the less there are times when it is convenient to present large numbers in this way within SQL Server.
Link back to this article : http://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 a comment   No login required !

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