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


T-SQL Parsing Crazy Eights


T-SQL Parsing Crazy Eights

Author
Message
Tom Garth
Tom Garth
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1239 Visits: 1499
I thought it was going to be a dull day.

Thanks,

Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

SanjayAttray
SanjayAttray
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4377 Visits: 1619
crispy cracker QOD.

SQL DBA.
Gatekeeper
Gatekeeper
SSC-Addicted
SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)

Group: General Forum Members
Points: 448 Visits: 888
The only reason I got it right was because it was multiple choice and eliminated the others. I had no idea the parser was nice about putting non-numerical values after the power delimiter (E).

/* Anything is possible but is it worth it? */
john.arnott
john.arnott
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1964 Visits: 3059
aboyce (9/22/2009)
The answer depends on where the query is run. If run from SQL Server Management Studio, you get the answer given. With the 9 columns being:
E (No name) ight Eight E8E Eight (No name) ight Eight
However, when I ran the same query in Microsoft Visual Studio's Query window I got different column names:
E E Eight Eight E8E Eight Expr1 Eight Eight

The data was the same in both cases.

Interesting point, aboyce. It looks to me as though VS parses in a more intuitive way. Where SSMS drops the letter "E" in cases where it's not interpreted as part of the numeric value (as in the second and third columns), VS retains it as part of the column name. I just tried this and see that Visual Studio first shows us a re-formatted query with explicit "AS" markers for the column aliases, wven before we hit "execute".
The original query:
Select 1.8 E,1.8E,1.Eight,3+8E8Eight, 
3+8 E8E,'Six'+'Seven' 'Eight','Six'+'Seven'+'Eight',
8.8Eight,'Six''Seven' 'Eight'


Visual Studio reformats as:
SELECT        1.8 AS E, 1.8 AS E, 1. AS Eight, 3 + 8E8 AS Eight, 3 + 8 AS E8E, 'Six' + 'Seven' AS 'Eight', 'Six' + 'Seven' + 'Eight' AS Expr1, 8.8 AS Eight, 'Six''Seven' AS 'Eight'


When cut and pasted to SSMS, of course this reformatted query gives the same column names as the final result in VS.

The important point here would seem to be that the environment from which a query is run can affect its output. I haven't tried embedding this sort of parsing mess into a program, but wouldn't be surprised if SQL Server treated it in yet another way.
sknox
sknox
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2596 Visits: 2828
john.arnott (9/22/2009)
...
The important point here would seem to be that the environment from which a query is run can affect its output. I haven't tried embedding this sort of parsing mess into a program, but wouldn't be surprised if SQL Server treated it in yet another way.


Actually, the important point here is that Visual Studio rewrites your query before sending it to the server. I've run into this several times, and more than once it's actually caused perfectly good queries to fail. That's one reason why I rarely use it.

I believe you'll find that SSMS is not parsing your code beyond utility commands like "GO", and if you sent the raw code programmatically to the server, you'd get the same results as from SSMS.
Tao Klerks
Tao Klerks
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1527 Visits: 1249
I considered trying to say that, but couldn't figure out how to do so as clearly as @sknox just did :-).

http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
KevinC.
KevinC.
Right there with Babe
Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)

Group: General Forum Members
Points: 785 Visits: 504
Nice, thought-provoking question.Cool
Hafiz-1142834
Hafiz-1142834
SSC-Enthusiastic
SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)

Group: General Forum Members
Points: 142 Visits: 46
Nice Question.
David Walker-278941
David Walker-278941
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 231
"The syntax parser is forgiving of missed spaces between a literal value and it's column alias."

Yes, but I'm not forgiving of superfluous apostrophes in the word "its". The sentence gets a Fail. "It's" means "it is" and nothing else.
Tao Klerks
Tao Klerks
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1527 Visits: 1249
David Walker-278941 (9/23/2009)
Yes, but I'm not forgiving of superfluous apostrophes in the word "its". The sentence gets a Fail. "It's" means "it is" and nothing else.

Ah but if you're going to be all finicky about it, "it's" can actually mean "it is" or "it has"...

http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
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