T-SQL Parsing Crazy Eights

  • john.arnott

    SSChampion

    Points: 11882

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

  • Joy Smith San

    SSC-Insane

    Points: 24875

    Good question.

  • ChiragNS

    One Orange Chip

    Points: 26137

    real cracker.

    "Keep Trying"

  • dgvozdetsky

    SSCrazy

    Points: 2464

    Very interesting and good question 🙂

  • RichB

    SSCrazy Eights

    Points: 9651

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

  • sjimmo

    SSChampion

    Points: 11139

    :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: 3378

    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: 4187

    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

    One Orange Chip

    Points: 29863

    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 says Can I join you?
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • aboyce

    SSC Enthusiast

    Points: 153

    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: 6173

    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

    crispy cracker QOD.

    SQL DBA.

  • Gatekeeper

    SSCommitted

    Points: 1583

    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

    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: 12215

    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.

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

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