Is there any cast operator for inserting comma (,) 1

[VMRao]

Hi all,
Can anybody tell me how to put a comma between the digits of a number which i got from the query.I mean is there any CAST/CONVERT/...? FUNCTION TO DO THIS.

Ex: I have to print 10000 as 10,000
7896 as 7,896
Thanz in Advance...
Vmrao.



Malli.
When the going gets tough,
Tough gets going..........

 

[WhiteKnight57t]

Yes search convert in BOL

==========================
Date is a way to show you care
Time is a great healer
DateTime is just damn confusing

Tim

 

[SQLBill]

CONVERT adds commas ONLY for MONEY and SMALLMONEY datatypes. You might try CONVERTing to MONEY using the appropriate STYLE to add the comma and then CONVERTing back.

-SQLBill

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine

Posting advice: FAQ481-4875

 

[dbomrrsm]

create table tab (t money)

insert into tab values (10000.00)

select convert(varchar (10),t,1) from tab

[blue]DBomrrsm[/blue]

 

[VMRao]

Thanks DBomrrsm,
My query is:

select convert(varchar (10),convert(money,sum(dis)),1) from master_table
where year(h_date) = '2001'
and cat in ('370','371','372')

---------
55,581.00

(1 row(s) affected)

I got (,) But still i have problem with decimal values.

Malli.
When the going gets tough,
Tough gets going..........

 

[dbomrrsm]

select * from ttt

gives

10000.0000
100.0200
10120.0200
1120.0200

select convert(varchar (10),convert(money,sum(t)),1)
from ttt

the same first line as your code gives:

21,340.06

what do you mean exactly that you:

have problem with decimal values.

[blue]DBomrrsm[/blue]

 

[VMRao]

I mean i don't want decimal value as the result.
I am looking for 21,340 instead of 21,340.06
sorry, i should have mentioned it in the last thread only.


Malli.
When the going gets tough,
Tough gets going..........

 

[SQLBill]

Remember, when you convert to VARCHAR you have to count commas and decimal point as part of the VARCHAR value.

-SQLBill

 

[dbomrrsm]

select convert (int,floor(sum(t)),1)
from ttt

rounding the money will first round the sum - if you need to always have it round down them you need:

select convert(varchar (20),convert(money,floor(sum(t)),1))
from ttt

if by chance you need it to always round up then you need:

select convert(varchar (20),convert(money,ceiling(sum(t)),1))
from ttt

[blue]DBomrrsm[/blue]

 

[dbomrrsm]

another way !!!

select substring(convert(varchar (10),convert(money,sum(t)),1),1,(
select len(convert(varchar (10),convert(money,sum(t)),1))-3
from ttt)
)
from ttt

[blue]DBomrrsm[/blue]

 

[VMRao]

Hi DBomrrsm,

Are you sure both queries you posted are correct?

VMrao

 

[VMRao]

Yes, you are right..I used the second query ...and got the result..

select substring(convert(varchar (10),convert(money,sum(dis)),1),1,(select len(convert(varchar (10),convert(money,sum(dis)),1 ) )-3 from master_file
where year(h_date) = '2001'
and cat in ('371','372','373')
and sex = 'f'
)
)from master_file where year(h_date) = '2001' and cat in ('371','372','373')
and sex = 'f'

----------
159,301

(1 row(s) affected)