Mixing
What is the best way to put commas into large numbers
Clash Royale CLAN TAG#URR8PPP
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;
up vote
7
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 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 right some heinous
left(right(vandalized_data),6),3) + ',' + right(left(vandalized_data),6),3)
function?
sql-server
asked Sep 5 at 17:57
James
904322
add a comment |Â
up vote
7
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 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 right some heinous
left(right(vandalized_data),6),3) + ',' + right(left(vandalized_data),6),3)
function?
sql-server
asked Sep 5 at 17:57
James
904322
You can use something like this post, by using CONVERT. This is usually something that I would do in the front end application, though. Unless you meant in the display grid in SSMS?
– Jacob H
Sep 5 at 18:01
@JacobH yea, the perfect thing would be commas in the display grid, then plain integers in the output.
– James
Sep 5 at 18:02
add a comment |Â
up vote
7
down vote
favorite
up vote
7
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 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 right some heinous
left(right(vandalized_data),6),3) + ',' + right(left(vandalized_data),6),3)
function?
sql-server
asked Sep 5 at 17:57
James
904322
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 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 right some heinous
left(right(vandalized_data),6),3) + ',' + right(left(vandalized_data),6),3)
function?
sql-server
asked Sep 5 at 17:57
James
904322
asked Sep 5 at 17:57
James
904322
asked Sep 5 at 17:57
James
904322
asked Sep 5 at 17:57
James
904322
904322
You can use something like this post, by using CONVERT. This is usually something that I would do in the front end application, though. Unless you meant in the display grid in SSMS?
– Jacob H
Sep 5 at 18:01
@JacobH yea, the perfect thing would be commas in the display grid, then plain integers in the output.
– James
Sep 5 at 18:02
add a comment |Â
You can use something like this post, by using CONVERT. This is usually something that I would do in the front end application, though. Unless you meant in the display grid in SSMS?
– Jacob H
Sep 5 at 18:01
@JacobH yea, the perfect thing would be commas in the display grid, then plain integers in the output.
– James
Sep 5 at 18:02
You can use something like this post, by using CONVERT. This is usually something that I would do in the front end application, though. Unless you meant in the display grid in SSMS?
– Jacob H
Sep 5 at 18:01
You can use something like this post, by using CONVERT. This is usually something that I would do in the front end application, though. Unless you meant in the display grid in SSMS?
– Jacob H
Sep 5 at 18:01
@JacobH yea, the perfect thing would be commas in the display grid, then plain integers in the output.
– James
Sep 5 at 18:02
@JacobH yea, the perfect thing would be commas in the display grid, then plain integers in the output.
– James
Sep 5 at 18:02
add a comment |Â
2 Answers
2
active
oldest
votes
up vote
11
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
19.5k1161101
add a comment |Â
up vote
15
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
answered Sep 5 at 18:04
David Browne - Microsoft
8,194622
add a comment |Â
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
11
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
19.5k1161101
add a comment |Â
up vote
11
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
19.5k1161101
add a comment |Â
up vote
11
down vote
accepted
up vote
11
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
19.5k1161101
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
19.5k1161101
answered Sep 5 at 18:16
sp_BlitzErik
19.5k1161101
answered Sep 5 at 18:16
sp_BlitzErik
19.5k1161101
answered Sep 5 at 18:16
sp_BlitzErik
19.5k1161101
19.5k1161101
add a comment |Â
add a comment |Â
up vote
15
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
answered Sep 5 at 18:04
David Browne - Microsoft
8,194622
add a comment |Â
up vote
15
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
answered Sep 5 at 18:04
David Browne - Microsoft
8,194622
add a comment |Â
up vote
15
down vote
up vote
15
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
answered Sep 5 at 18:04
David Browne - Microsoft
8,194622
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
answered Sep 5 at 18:04
David Browne - Microsoft
8,194622
edited Sep 6 at 13:46
answered Sep 5 at 18:04
David Browne - Microsoft
8,194622
answered Sep 5 at 18:04
David Browne - Microsoft
8,194622
answered Sep 5 at 18:04
David Browne - Microsoft
8,194622
8,194622
add a comment |Â
add a comment |Â
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f216816%2fwhat-is-the-best-way-to-put-commas-into-large-numbers%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
You can use something like this post, by using CONVERT. This is usually something that I would do in the front end application, though. Unless you meant in the display grid in SSMS?
– Jacob H
Sep 5 at 18:01
@JacobH yea, the perfect thing would be commas in the display grid, then plain integers in the output.
– James
Sep 5 at 18:02