T-SQL Parsing Crazy Eights

Question

Post reply

T-SQL Parsing Crazy Eights

john.arnott

SSChampion

Points: 11882
More actions
September 21, 2009 at 10:14 pm

#214596

Comments posted to this topic are about the item T-SQL Parsing Crazy Eights

Viewing 15 posts - 1 through 15 (of 39 total)

You must be logged in to reply to this topic. Login to reply

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 1

Joy Smith San

SSC-Insane

Points: 24877

September 21, 2009 at 10:42 pm

#1056551

Good question.

ChiragNS One Orange Chip Points: 26137 More actions · Answer 2

ChiragNS

One Orange Chip

Points: 26137

September 22, 2009 at 12:34 am

#1056571

real cracker.

"Keep Trying"

dgvozdetsky SSCrazy Points: 2464 More actions · Answer 3

dgvozdetsky

SSCrazy

Points: 2464

September 22, 2009 at 2:09 am

#1056605

Very interesting and good question 🙂

RichB SSCrazy Eights Points: 9651 More actions · Answer 4

haha, someone should submit that to the daily wtf... :hehe:

sjimmo SSChampion Points: 11139 More actions · Answer 5

:w00t:Great question - thought provoking. Had to re-read a couple of times

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

BudaCli Hall of Fame Points: 3384 More actions · Answer 6

This really got me confused, for a sec I almost thought that it was an error ans

What you don't know won't hurt you but what you know will make you plan to know better

Tao Klerks SSCarpal Tunnel Points: 4297 More actions · Answer 7

Very fun Q 🙂

I particularly like "3+8E8Eight"

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.

webrunner SSC-Dedicated Points: 31713 More actions · Answer 8

Great question. I couldn't believe that such a wacked-out query would work.

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

aboyce SSC Enthusiast Points: 153 More actions · Answer 9

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.

Tom Garth SSCertifiable Points: 6193 More actions · Answer 10

I thought it was going to be a dull day.

Thanks,

Tom Garth
Vertical Solutions[/url]

"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 SSChampion Points: 13157 More actions · Answer 11

SanjayAttray

SSChampion

Points: 13157

September 22, 2009 at 9:51 am

#1056906

crispy cracker QOD.

SQL DBA.

Gatekeeper SSCommitted Points: 1633 More actions · Answer 12

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 SSChampion Points: 11882 More actions · Answer 13

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 SSChampion Points: 12388 More actions · Answer 14

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.