Need to format decimal output with commas

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

SQL Server 2000 Forums

Transact-SQL (2000)

Need to format decimal output with commas

Author

Topic

bigwhacker
Starting Member

6 Posts

Posted - 2004-09-23 : 14:00:46

I have looked everywhere - decided to finally post the question.
How can you format the output from a select where the data in question is defined as say decimal(15,4) - and you wand to see a comma separator for the thousands?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-23 : 14:05:06

You shouldn't add this to your query. If you want it displayed in a certain format, then the presentation layer (your application) should handle that not SQL.

Tara

bigwhacker
Starting Member

6 Posts

Posted - 2004-09-23 : 14:11:30

I know that is the normal method - but sometimes I just need a simple "report" without going through something like Access or Crystal or ...

Oracle provides a method of formatting output - does SQLServer?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-23 : 14:13:19

Nothing is built-in for this in SQL Server. You'd have to write a custom function to do this.

Tara

bigwhacker
Starting Member

6 Posts

Posted - 2004-09-23 : 14:20:31

Thanks,
That is what I thought - just didn't know if it was somewhere and I had missed it.

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-23 : 14:23:33

Ummm...


DECLARE @x money
SELECT @x = 1234567890.1234
SELECT CONVERT(varchar(25),@x,1)

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-23 : 14:24:56

Damn, didn't know you could do that. I've only use the styles with CONVERT when using datetime data.

Tara

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-23 : 14:37:10

damn... me neither....

here was what i was going to offer... sheeesh


Declare @a decimal(15,4)

Select @a = 22123456789.01

Select 
	@a,
	case when charindex('.',@a)>=13 then ',' + left(reverse(left(reverse(left(@a,charindex('.',@a)-1)),12)),3) when charindex('.',@a)>=11 then left(reverse(left(reverse(left(@a,charindex('.',@a)-1)),12)),13-charindex('.',@a)+1) else '' end +
	case when charindex('.',@a)>=10 then ',' + left(reverse(left(reverse(left(@a,charindex('.',@a)-1)),9)),3) when charindex('.',@a)>=8 then left(reverse(left(reverse(left(@a,charindex('.',@a)-1)),9)),10-charindex('.',@a)+1) else '' end +
	case when charindex('.',@a)>=7 then ',' + left(reverse(left(reverse(left(@a,charindex('.',@a)-1)),6)),3) when charindex('.',@a)>=5 then left(reverse(left(reverse(left(@a,charindex('.',@a)-1)),6)),7-charindex('.',@a)+1) else '' end +
	case when charindex('.',@a)>=4 then ',' + reverse(left(reverse(left(@a,charindex('.',@a)-1)),3)) when charindex('.',@a)>=2 then left(reverse(left(reverse(left(@a,charindex('.',@a)-1)),3)),4-charindex('.',@a)+1) else '' end
Select @a = 24680.01

Select 
	@a,
	case when charindex('.',@a)>=13 then ',' + left(reverse(left(reverse(left(@a,charindex('.',@a)-1)),12)),3) when charindex('.',@a)>=11 then left(reverse(left(reverse(left(@a,charindex('.',@a)-1)),12)),13-charindex('.',@a)+1) else '' end +
	case when charindex('.',@a)>=10 then ',' + left(reverse(left(reverse(left(@a,charindex('.',@a)-1)),9)),3) when charindex('.',@a)>=8 then left(reverse(left(reverse(left(@a,charindex('.',@a)-1)),9)),10-charindex('.',@a)+1) else '' end +
	case when charindex('.',@a)>=7 then ',' + left(reverse(left(reverse(left(@a,charindex('.',@a)-1)),6)),3) when charindex('.',@a)>=5 then left(reverse(left(reverse(left(@a,charindex('.',@a)-1)),6)),7-charindex('.',@a)+1) else '' end +
	case when charindex('.',@a)>=4 then ',' + reverse(left(reverse(left(@a,charindex('.',@a)-1)),3)) when charindex('.',@a)>=2 then left(reverse(left(reverse(left(@a,charindex('.',@a)-1)),3)),4-charindex('.',@a)+1) else '' end

Corey

bigwhacker
Starting Member

6 Posts

Posted - 2004-09-23 : 15:36:32

Thanks!
I had studied the convert function - but zipped right past that section!

Kristen
Test

22859 Posts

Posted - 2004-09-24 : 04:43:19

Note that it only works for MONEY datatype, so you may need to convert to that first ...

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources