Best way to put commas into large numbers

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;

up vote
10
down vote

favorite

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.

edited Sep 20 at 5:45

Paul Whiteâ™¦

46.8k14252401

asked Sep 5 at 17:57

James

970525

add a commentÂ |Â

up vote
10
down vote

favorite

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.

edited Sep 20 at 5:45

Paul Whiteâ™¦

46.8k14252401

asked Sep 5 at 17:57

James

970525

add a commentÂ |Â

up vote
10
down vote

favorite

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.

edited Sep 20 at 5:45

Paul Whiteâ™¦

46.8k14252401

asked Sep 5 at 17:57

James

970525

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.

sql-server number-formatting

edited Sep 20 at 5:45

Paul Whiteâ™¦

46.8k14252401

asked Sep 5 at 17:57

James

970525

edited Sep 20 at 5:45

Paul Whiteâ™¦

46.8k14252401

asked Sep 5 at 17:57

James

970525

edited Sep 20 at 5:45

Paul Whiteâ™¦

46.8k14252401

edited Sep 20 at 5:45

Paul Whiteâ™¦

46.8k14252401

edited Sep 20 at 5:45

Paul Whiteâ™¦

46.8k14252401

asked Sep 5 at 17:57

James

970525

asked Sep 5 at 17:57

James

970525

asked Sep 5 at 17:57

James

970525

add a commentÂ |Â

2 Answers
2

active

oldest

votes

up vote
14
down vote

accepted

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'')

answered Sep 5 at 18:16

sp_BlitzErik

20k1162101

add a commentÂ |Â

up vote
18
down vote

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

edited Sep 6 at 13:46

answered Sep 5 at 18:04

David Browne - Microsoft

8,444622

add a commentÂ |Â

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: false,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);

);

draft saved

draft discarded

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f216816%2fbest-way-to-put-commas-into-large-numbers%23new-answer', 'question_page');

);

Post as a guest

Name

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

up vote
14
down vote

accepted

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'')

answered Sep 5 at 18:16

sp_BlitzErik

20k1162101

add a commentÂ |Â

up vote
14
down vote

accepted

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'')

answered Sep 5 at 18:16

sp_BlitzErik

20k1162101

add a commentÂ |Â

up vote
14
down vote

accepted

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'')

answered Sep 5 at 18:16

sp_BlitzErik

20k1162101

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'')

answered Sep 5 at 18:16

sp_BlitzErik

20k1162101

answered Sep 5 at 18:16

sp_BlitzErik

20k1162101

answered Sep 5 at 18:16

sp_BlitzErik

20k1162101

answered Sep 5 at 18:16

sp_BlitzErik

20k1162101

add a commentÂ |Â

up vote
18
down vote

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

edited Sep 6 at 13:46

answered Sep 5 at 18:04

David Browne - Microsoft

8,444622

add a commentÂ |Â

up vote
18
down vote

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

edited Sep 6 at 13:46

answered Sep 5 at 18:04

David Browne - Microsoft

8,444622

add a commentÂ |Â

up vote
18
down vote

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

edited Sep 6 at 13:46

answered Sep 5 at 18:04

David Browne - Microsoft

8,444622

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

edited Sep 6 at 13:46

answered Sep 5 at 18:04

David Browne - Microsoft

8,444622

edited Sep 6 at 13:46

answered Sep 5 at 18:04

David Browne - Microsoft

8,444622

answered Sep 5 at 18:04

David Browne - Microsoft

8,444622

answered Sep 5 at 18:04

David Browne - Microsoft

8,444622

add a commentÂ |Â

draft saved

draft discarded

draft saved

draft discarded

Post as a guest

Name

搜尋此網誌

mjhjmtu