SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Thousand Seperator Magic


Thousand Seperator Magic

Author
Message
karthik M
karthik M
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2931 Visits: 2584
All,

I have recently faced the below scenario.
i.e to add comma as the thousand seperator

I have searched here and found the below query



select convert(varchar,cast(12345678918 as money),1)




how this query is adding ',' ?

even i tried different combination of numbers which start with 1, all of them adding ',' . But when i use other than 1, it didn't. how? what is the magic behind this '1' ?

karthik
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39335 Visits: 38538
karthik M (12/1/2012)
All,

I have recently faced the below scenario.
i.e to add comma as the thousand seperator

I have searched here and found the below query



select convert(varchar,cast(12345678918 as money),1)




how this query is adding ',' ?

even i tried different combination of numbers which start with 1, all of them adding ',' . But when i use other than 1, it didn't. how? what is the magic behind this '1' ?



From Books Online, CONVERT function:



money and smallmoney Styles
When expression is money or smallmoney, style can be one of the values shown in the following table. Other values are processed as 0.

Value Output
0 (default)
No commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 4235.98.

1
Commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 3,510.92.

2
No commas every three digits to the left of the decimal point, and four digits to the right of the decimal point; for example, 4235.9819.

126
Equivalent to style 2 when converting to char(n) or varchar(n)



Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39335 Visits: 38538
CELKO (12/1/2012)
to add comma as the thousand separator


NO! Display formatting is done in the presentation layers of any tiered architecture. The proprietary MONEY data type is left-over from the 1970's Sybase dialect. It formats numeric values to look like they had been written with a COBOL PICTURE clause.

01 FOOBAR PICTURE $999,999,999,999V9999

We do not do this in SQL any more.



Joe, you're a dinosaur like me. Most people on this forum probably don't even know what COBOL is.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
CapnHector
CapnHector
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1335 Visits: 1789
Lynn Pettis (12/1/2012)
CELKO (12/1/2012)
to add comma as the thousand separator


NO! Display formatting is done in the presentation layers of any tiered architecture. The proprietary MONEY data type is left-over from the 1970's Sybase dialect. It formats numeric values to look like they had been written with a COBOL PICTURE clause.

01 FOOBAR PICTURE $999,999,999,999V9999

We do not do this in SQL any more.



Joe, you're a dinosaur like me. Most people on this forum probably don't even know what COBOL is.


Isn't that a shade of blue :-D as far as why to do it, there are some limited times when i want my results window to look preaty on a couple of queries i run when checking our platform. I would prefer to leave it to the UI but when the UI does not have the options (SSMS) in the SQL is the only place to really do it.


For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39335 Visits: 38538
CELKO (12/3/2012)
Joe, you're a dinosaur like me. Most people on this forum probably don't even know what COBOL is.


And most of them have never see a punch card or used a mag tape drive. But they keep re-inventing them in SQL. Laugh


And that's part of the problem, they have no idea what you are even talking about.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
dogramone
dogramone
Right there with Babe
Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)

Group: General Forum Members
Points: 743 Visits: 3740
And unfortunately none of them are intelligent enough to investigate. Oh for the good old days when IT was a black box that no one got involved in unless they were truly inspired. Now it's become jut like accounting, a degree and job only and in it jut for the money.

Joe please keep hitting them with a hammer, eventually it might imprint somewhere.



Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8597 Visits: 7660
dogramone (12/3/2012)
And unfortunately none of them are intelligent enough to investigate. Oh for the good old days when IT was a black box that no one got involved in unless they were truly inspired. Now it's become jut like accounting, a degree and job only and in it jut for the money.

Joe please keep hitting them with a hammer, eventually it might imprint somewhere.


Oh, don't worry, he definately makes an impression.

You bring up a good point though. Eventually (we're getting there) the tools become good enough that the average person can roll their own for small projects. They may not be a professional cabinet maker but the average person just needs to be able to slap their IKEA bookshelf together. Quality will always be required at some level, but every contacts database out there really doesn't (usually) need top-end computer science knowledge. However, I'm helping to horribly derail this poor thread, and probably shouldn't soapbox that topic here. Though, I do have a new blog post idea. :-)


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39335 Visits: 38538
dogramone (12/3/2012)
And unfortunately none of them are intelligent enough to investigate. Oh for the good old days when IT was a black box that no one got involved in unless they were truly inspired. Now it's become jut like accounting, a degree and job only and in it jut for the money.

Joe please keep hitting them with a hammer, eventually it might imprint somewhere.


I'm sorry, but Mr. Celko insults the intelligence of others enough, we don't need someone else following in his unprofessional foot steps.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search