sknox (1/3/2013)
roger.plowman (1/3/2013)
This issue is yet another example of why SQL is one of the worst-designed languages of all time from a syntactic POV.Would it have killed the designers to create two string delimiters that could be interchanged (ala BASIC) and reserved square brackets for field/table delimiting?
So that when you have a string which contains both delimiters as literals, you have double the problem? Syntactically, having two delimiters that mean the same thing is a problem, not a solution.
And while we're at it to use #'s to delimit dates/times (ala MS Access)?
"ala MS Access" == NO. Besides, there's plenty of good documentation on date formatting in SQL, and simply changing 's to #s wouldn't give you any better conversion rate.
Oh, and use a dedicated "escape" character instead of doubling the escaped character? Sheesh!
On this one, I agree with you -- in principle at least. From a syntax standpoint, it makes sense to have a specific escape character, like regular expressions do, for example. But what happens when you need to include the escape character as a literal? Double the escaped character, again.
In the case of T-SQL and string literals, the only character which would ever need to be escaped is '. So the options were:
a) create an escape character (let's say \) and then use that character to escape every literal ' and \ (i.e, have possible two escape clauses: \' and \\), or
b) use ' as the escape character for itself and have one possible escape clause ('').
So they again opted for the simpler solution.
I do see one benefit for using an escape character that isn't your delimiter. '\\' is quite a bit easier to follow than ''''. Or for that mater '\\\\' is much easier than ''''''. On the whole though I agree with you. A character had to be picked as an escape character and regardless of what character it was we were going to have some level of problems with it.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]