• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4747
  • Last Modified:

How do I Return Numeric Data with a Comma Thousands Separator in SQL Server

I am trying to display a number with commas in an SQL Server 2000 View. I've read several solutions on various websites, but none are working for me.

Input: 1234567.89
Desired output: 1,234,567 (I don't want any decimal places).

I have tried:

REPLACE(CONVERT(VARCHAR(20), CAST(ROUND(dbo.table1.area, 0) AS MONEY), 1), '.00', '')

But I keep getting an SQL Server Enterprise Manager error window with "Data type error in expression".

I tried

REPLACE(cast (CONVERT(VARCHAR(20), CAST(ROUND(dbo.table1.area, 0) AS MONEY), 1) as VARCHAR, '.00', '')

with the same result.  Geez, it was so easy in Cobol !!!!!!

Help!
0
berniepet
Asked:
berniepet
1 Solution
 
appariCommented:
try something like this

declare @val numeric(10,2);
select @val=123456.44;
select @val, convert(varchar,convert(money,'$' + convert(varchar,@val)),128)


so your case it should be something like this
convert(varchar,convert(money,'$' + convert(varchar, dbo.table1.area)),128)
0
 
trainsdseCommented:
create function dbo.myRound(@val decimal(30,2))
returns varchar(500)
as
begin
declare @rounded decimal(30,2)
set @rounded = round(@val,0)
declare @return varchar(500)
set @return = convert(varchar,cast(@rounded as money),1)
set @return = left(@return,len(@return)-3)
return @return
end
go
select dbo.myRound(1234567.89)
--returns 1,234,568  ***notice the last char is an 8 due to rounding up

If you don't want it to round up like that replace the set @rounded line with

set @rounded = @val

You can then call this function from your query as in select dbo.myRound(colname)..... from ....
0
 
Anthony PerkinsCommented:
Have you considered using your front-end application to do this?
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now