Comma for Thousands Separator - (*&@#$/ 1

[EricSql]

I am frustrated - I want to have numerical results returned with the ',' - does anyone know how to do this in SQL view?

 

[tlbroadbent]

Comma is a character so SQL cannot return a numerical result with comma. However, SQL can convert a money column or value to a character data type and insert the commas.

Select '$'+convert(varchar(15),$100340.12,1)
Select '$'+convert(nvarchar(15),MoneyCol,1)
From tbl
Select '$'+convert(varchar(15),convert(money,@var),1)
Select '$'+convert(varchar(15),convert(money,12456),1)


Terry Broadbent

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[EricSql]

So this does seem to work - but how do I get it to show with no decimals? I am getting the following returned: 123,456.00 - Thanks!!

 

[tlbroadbent]

This is ugly but so are some of the alternatives.

Select reverse(substring(reverse('$'+convert(varchar(15),MoneyCol,1)),4,11)) Terry Broadbent

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[EricSql]

That worked like a charm!

But now an even better question - what if I want to use a SUM of the values and want it returned with a comma? It does not want to let me do any of the mathematical functions on it once I have converted to VARCHAR.

Any ideas?

Thanks.

 

[tlbroadbent]

Put the SUM function inside all the other functions.

Select reverse(substring(reverse('$'+convert(varchar(15),Sum(MoneyCol),1)),4,11)) Terry Broadbent

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[EricSql]

Terry - THANK YOU!!!! This all worked!! s-)