SQL Server 2008 Thousands Separator for a Column

SQL Server 2008 thousands separator for a column

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

thousands separator in sql

This is purely presentation matter and should be done in application layer.

If you need to do this at database level(maybe legacy application) the solution is dependent on which RDBMS you use.

SQL Server 2012+ FORMAT:

SELECT FORMAT(123455213.32, '###,###,###.##')

LiveDemo

Format number with space as thousand separator

For SQL Server 2008, I think your best bet for a "nicer" solution within SQL Server would be to write a custom CLR function to handle the formatting based on region/locale.

For SQL Server 2012 or later, the FORMAT function has been introduced.

A more concise way to format COUNT result with thousands separator?

Not the most elegant, but you can remove the trailing .00 with replace.

SELECT REPLACE(CONVERT(VARCHAR, CAST(COUNT([id]) AS MONEY), 1), '.00', '')
FROM tbl

How do I format a number with commas in T-SQL?

In SQL Server 2012 and higher, this will format a number with commas:

select format([Number], 'N0')

You can also change 0 to the number of decimal places you want.

Format a number with commas but without decimals in SQL Server 2008 R2?

DECLARE @val varchar(50)

set @val = CONVERT(varchar(50), CAST(1112 AS money), 1)
SELECT  left(@val, len(@val) - 3)

This also works with digits after the decimal point:

DECLARE @val varchar(50)

set @val = CONVERT(varchar(50), CAST(1112.56 AS money), 1)
SELECT  left(@val, len(@val) - 3)

Note: as @Mahmoud Gamal points out, formatting is often more suited to be performed in the front-end.

What is the best way to reformat this piece of SQL to avoid the thousands separator

You could probably remove the format function. Then if required add convert(decimal(9,2), YourColumn)

---

Related Topics