Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

T-SQL Parsing Crazy Eights Expand / Collapse
Author
Message
Posted Tuesday, September 22, 2009 9:40 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 4, 2011 7:20 AM
Points: 977, Visits: 1,499
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
Post #791922
Posted Tuesday, September 22, 2009 9:51 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
crispy cracker QOD.

SQL DBA.
Post #791932
Posted Tuesday, September 22, 2009 9:57 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, July 27, 2014 3:54 PM
Points: 393, Visits: 811
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? */
Post #791939
Posted Tuesday, September 22, 2009 11:39 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 27, 2014 6:26 AM
Points: 1,521, Visits: 3,036
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.
Post #792047
Posted Tuesday, September 22, 2009 12:19 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:48 AM
Points: 1,304, Visits: 1,672
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.
Post #792088
Posted Tuesday, September 22, 2009 12:43 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 25, 2014 6:37 AM
Points: 1,385, Visits: 1,242
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.
Post #792101
Posted Tuesday, September 22, 2009 4:53 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, December 7, 2010 12:55 AM
Points: 771, Visits: 504
Nice, thought-provoking question.
Post #792257
Posted Wednesday, September 23, 2009 4:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 12, 2014 3:26 AM
Points: 136, Visits: 44
Nice Question.
Post #792509
Posted Wednesday, September 23, 2009 8:23 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 4, 2013 3:54 PM
Points: 67, Visits: 230
"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.
Post #792680
Posted Wednesday, September 23, 2009 8:43 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 25, 2014 6:37 AM
Points: 1,385, Visits: 1,242
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.
Post #792699
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse