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.