10

I've started a new job and it involves looking at a bunch of big numbers. Is there an easy way to add commas to an int or decimal field to make it readable?

For example, SQL Server outputs the column on the left, but for my own sanity, I need it to look like the one on the right:

2036150 -> 2,036,150  

...or would I have to write some heinous

left(right(vandalized_data),6),3) + ',' + right(left(vandalized_data),6),3)

function?

The perfect thing would be commas in the display grid, then plain integers in the output.

15

If you're on SQL Server 2012+, and you want to do it (without decimal places):

SELECT FORMAT(2036150, N'N0')

If you're on an earlier version, you have to jump through some hoops:

SELECT REPLACE(CONVERT(NVARCHAR(30), CAST((2036150) AS MONEY), 1), N'.00', N'')
18

TSQL's FORMAT function can do that. EG

select format(1232131312342132123.2222,'0,###.##')

outputs

1,232,131,312,342,132,123.22

If you want to use a different separator, you still use , which has a special meaning in .Net format string, and then replace the , with something else. EG:

select replace(format(123445678.2222,'#,###.##'),',','_')

outputs

123_445_678.22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.