SQL to retrieve a number showing the thousand sep

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL to retrieve a number showing the thousand sep

Author

Topic

SACK
Starting Member

16 Posts

Posted - 2009-06-28 : 08:19:06

Hi

Could someone please advise if it is possible to retrieve a number from the db so that the number displays the 'thousand seperator'?

I can format excel to show the same after the data has been exported to excel but wondering if the number can be retrieved with the ',' already showing.

Someone suggested
select convert(varchar(10), cast(1234.678 as money), 1)

but I don't know how to use this.

Regards
Sheraz Chowdry

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-28 : 08:21:41

it is better to do it in Excel rather than in SQL

if you want to do it SQL, convert your column to money and then to string.


select convert(varchar(10), convert(money, yourcolumn), 1)
from   yourtable

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-28 : 12:57:45

quote:
Originally posted by SACK

Hi

Could someone please advise if it is possible to retrieve a number from the db so that the number displays the 'thousand seperator'?

I can format excel to show the same after the data has been exported to excel but wondering if the number can be retrieved with the ',' already showing.

Someone suggested
select convert(varchar(10), cast(1234.678 as money), 1)

but I don't know how to use this.

Regards
Sheraz Chowdry

you dont need to do this in sql query. this is a formatting issue which should be handled at your presentation layer using format functions. there are various currency formatting functions which will do this for you. b/w what front end are you using?

SACK
Starting Member

16 Posts

Posted - 2009-06-29 : 01:48:13

Thanks.

So

select convert(varchar(10), convert(money, yourcolumn), 1)
from yourtable

is changing the type of the column? Could you please explain this a bit.

I didn't know you can do this for a column that already has data.

SACK
Starting Member

16 Posts

Posted - 2009-06-29 : 02:35:19

Thanks Vishak. I know this can be done in excel but just wanted to know if this was possible in sql?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-29 : 04:20:21

quote:
Originally posted by SACK

Thanks.

So

select convert(varchar(10), convert(money, yourcolumn), 1)
from yourtable

is changing the type of the column? Could you please explain this a bit.

I didn't know you can do this for a column that already has data.

it is changing the data type of the result. Your original table is not affected

KH
[spoiler]Time is always against us[/spoiler]

SACK
Starting Member

16 Posts

Posted - 2009-06-29 : 05:44:31

OK thanks for the explanation. Just one more question.

If I have select datetime,
sum(terminating_count) "Terminating Total"
from mytable
where XYZ
group by datetime

Where will
'select convert(varchar(10), convert(money, yourcolumn), 1)
from yourtable'

fit in?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-29 : 05:50:50

[code]
select datetime,
convert(varchar(10), convert(money, sum(terminating_count)), 1) "Terminating Total"
from mytable
where XYZ
group by datetime[/code]

KH
[spoiler]Time is always against us[/spoiler]

SACK
Starting Member

16 Posts

Posted - 2009-06-29 : 06:01:28

Thanks.

I tried this but it gives a 'Missing Expression' error with varchar(10) highlighted.

SACK
Starting Member

16 Posts

Posted - 2009-06-29 : 06:05:39

OK just to add to my previous post, the column used in the query is of type NUMBER so I replaced VARCHAR(10) with NUMBER and then also enclosed NUMBER and MONEY in quotes.

But it still won't work.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-29 : 06:06:55

can you post the query that you used ?

KH
[spoiler]Time is always against us[/spoiler]

SACK
Starting Member

16 Posts

Posted - 2009-06-29 : 06:12:10

select datetime,
convert(NUMBER, convert(MONEY, sum(terminating_count)), 1) "Terminating Total"
from schema.table
where datetime between trunc(sysdate)-1 and trunc(sysdate)-1/24
and caption not in ('mar2','mar1','SMQ'','SM','S')
group by datetime

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-29 : 06:40:09

You are not using Microsoft SQL Server, are you?

N 56°04'39.26"
E 12°55'05.63"

SACK
Starting Member

16 Posts

Posted - 2009-06-29 : 06:44:21

No. Wrong forum!! Apologies. Using Oracle.

SACK
Starting Member

16 Posts

Posted - 2009-06-29 : 07:26:24

Please help me on this and then I'll make sure I post in the correct forum...As my queries were simply SQL related, I chose this forum but I suppose this forum is not the correct one.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-29 : 07:48:29

We have given you the correct Microsoft SQL Server answer.
The CONVERT implementation in Oracle does seem to be different implemented than the Microsoft CONVERT implementation.

The anwer given 06/29/2009 : 05:50:50 will work on Microsoft SQL Server.
select convert(varchar(100), convert(money, 2340450), 1) AS [Terminating Total]

N 56°04'39.26"
E 12°55'05.63"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-29 : 08:14:19

try posting at dbforums.com for question on Oracle

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-30 : 04:20:14