SQL Server 2008 thousands separator for a column

Question

I have a column called TotalArea and its format is numeric (12,2).

I want it to display the numbers with a thousand separator so when I

select TotalArea from table

to show me a format like 1,234.00.

How could I do that? Thanks!

Solution 1

Try this way:

SELECT REPLACE(CONVERT(VARCHAR, CONVERT(MONEY, TotalArea), 1), '.00', '') 
FROM table

or

SELECT CAST(CONVERT(VARCHAR, CAST(123456 AS MONEY), 1) AS VARCHAR)
FROM table

Solution 2

SELECT FORMAT(12345,'#,0.00');

SELECT FORMAT(TotalArea,'#,0.00') from table;

Reference: https://msdn.microsoft.com/en-us/library/ee634206(v=sql.105).aspx

Solution 3

Formatting numbers for display is something that should be done in the display layer, and not within the database. So, in whatever application this data ends up being used, you should format it there. Management Studio, unfortunately, does not offer much control in this regard.

Solution 4

I know the question is for sql server 2008 but if you have sql server 2012+, you can use format like so:

SELECT FORMAT(12345.5634, 2);

Reference: https://www.w3schools.com/sql/func_mysql_format.asp

View more solutions