Clever Geek Handbook

Make a select statement output a number with commas

I am using SQL Server and am trying to figure out how to get a select statement to output a comma separated number. I can only find how to convert number to number with commas, which is not what I want. The following code is the code I am trying to get as a number with commas. Right now when you execute the statement it returns 750,000.00 and I want it to return 750,000.00.

code:

select max(c.salary) as MaxSalary
from #Career c inner join
     #Member m
    on c.Member_ID = m.Member_ID
where m.age < 35
+3


source to share


4 answers


You can use FORMAT()

for SQL Server 2012 forward



select FORMAT(750000.00, 'N', 'en-us') AS 'Numeric Format' 


declare @table table(salary decimal(16,4))
insert into @table
values
(1234.00),
(64423.55)

select FORMAT(sum(salary), 'N', 'en-us') AS 'Numeric Format' 
from @table

--or using your tables
select FORMAT(max(c.salary), 'N', 'en-us') AS 'Numeric Format' 
from #Career c inner join
     #Member m
    on c.Member_ID = m.Member_ID
where m.age < 35
+2


source


This may be the old-fashioned way, but converting to money and then converting to varchar is the traditional method:



select convert(varchar(255), convert(money, max(salary)), 1) as MaxSalary
+1


source


you can use the format if your dataset is minimal like below

select format(75000.00, 'N')

You can do it as shown below:

select Format(max(c.salary),'N') as MaxSalary
from #Career c inner join
  #Member m
  on c.Member_ID = m.Member_ID
where m.age < 35
0


source


Assuming SQL Server 2012+, you get what you need:

  SELECT FORMAT([COLUMN], 'N#')

where # is the number of decimal places. Place N0 for 0, N2 for 2, etc.

SQL Server 2008 R2:

  SELECT REPLACE(CONVERT(varchar(20), (CAST([COLUMN] AS MONEY)), 1), '.00', '')
0


source






More articles:


All Articles