January 2, 2013 at 9:53 pm
Comments posted to this topic are about the item Single Quotation Marks in SQL
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]
January 2, 2013 at 9:59 pm
I guess I have never been a fan of triple quoting, I usually use CHAR(39) I find it is a lot easier to read for others:
SELECT 'O' + CHAR(39) + 'Neal'
Just a thought, nice article though
January 3, 2013 at 2:03 am
Answer is
DECLARE @topsql nvarchar(200)
SET @topsql =
'DECLARE @quotedvar nvarchar(100) ' + char(13) +
'DECLARE @sql nvarchar(1000) ' + char(13) +
'SET @quotedvar = ''O''''Neil''' + char(13) +
'SET @sql = ''PRINT '''''' + REPLACE(@quotedvar,'''''''','''''''''''') + '''''''''+ char(13) +
'PRINT @sql ' + char(13) +
'EXEC sp_executesql @sql '
PRINT @topsql
PRINT '-------'
EXEC sp_executesql @topsql
Finger Crossed 🙂
Vandana
January 3, 2013 at 2:04 am
Following code will do the trick;
DECLARE @topsql nvarchar(200)
SET @topsql =
'DECLARE @quotedvar nvarchar(100) ' + char(13) +
'DECLARE @sql nvarchar(1000) ' + char(13) +
'SET @quotedvar = ''O''''Neil'' ' + char(13) +
'SET @sql = ''PRINT '''''' + REPLACE(@quotedvar,'''''''','''''''''''') + '''''''' ' + char(13) +
'PRINT @sql ' + char(13) +
'EXEC sp_executesql @sql '
PRINT @topsql
PRINT '-------'
EXEC sp_executesql @topsql
Thanks, Hasham Niaz
January 3, 2013 at 4:10 am
I have frequently had to script out Stored Procs that use dynamic SQL, and the joys of altering the number of quotes defies description.
Using the 'Generate Scripts' option within SSMS is a useful solution for such situations and a lot of people don't realise that it can be used for such. It will create a script with the correct number of quotes, providing an easy way to script such things for moving to different databases.
January 3, 2013 at 4:14 am
Nice, if that method can be applied cleanly and consistently across all SPs and all apps.
Unfortunately, many of us work in sloppy production environments with a number of development people with differing abilities, working on different apps, web pages and SPs with different coding standards. In that kind of situation, it's all too easy for folks to forget the O'Neil scenarios and forget to code defensively in both web code and SPs.
On the basis that prevention is better than a painful cure, or just belt-and-braces, I have a background SQL Agent task that runs every night on the "usual suspects" (Surname and address columns) to replace single apostrophes (CHAR(39)) with a Grave accent (CHAR(96))
So, O'Neil becomes O`Neil.
I appreciate this may offend some people, because it should not be necessary, but it does help a little to prevent public-facing apps from failing in an embarrasing way.
January 3, 2013 at 4:16 am
How about using Quotename...?
SELECT QUOTENAME('o''neil', '''')
January 3, 2013 at 5:14 am
Michael R. OBrien Jr (1/2/2013)
I guess I have never been a fan of triple quoting, I usually use CHAR(39) I find it is a lot easier to read for others:SELECT 'O' + CHAR(39) + 'Neal'
Just a thought, nice article though
+1
January 3, 2013 at 6:16 am
johnbrown105 56149 (1/3/2013)
Michael R. OBrien Jr (1/2/2013)
I guess I have never been a fan of triple quoting, I usually use CHAR(39) I find it is a lot easier to read for others:SELECT 'O' + CHAR(39) + 'Neal'
Just a thought, nice article though
+1
+2
In addition, I try to use Powershell Here-Strings wherever I can instead of dynamic SQL.
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
January 3, 2013 at 6:34 am
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? And while we're at it to use #'s to delimit dates/times (ala MS Access)?
Oh, and use a dedicated "escape" character instead of doubling the escaped character? Sheesh!
Sorry, this is one of (many) pet peeves I have with T-SQL.
January 3, 2013 at 7:03 am
MG-148046 (1/3/2013)
johnbrown105 56149 (1/3/2013)
Michael R. OBrien Jr (1/2/2013)
I guess I have never been a fan of triple quoting, I usually use CHAR(39) I find it is a lot easier to read for others:SELECT 'O' + CHAR(39) + 'Neal'
Just a thought, nice article though
+1
+2
In addition, I try to use Powershell Here-Strings wherever I can instead of dynamic SQL.
Unless I'm misunderstanding what you are doing you will still need to keep multiplying the number of CHAR(39)s that you are using. So you would have
REPLACE(@quotedvar, CHAR(39), CHAR(39)+CHAR(39))
Certainly easier to read but I'm not sure if it wouldn't confuse me even more once I got down into multiple layers of dynamic SQL. i.e. Using dynamic SQL to generate more dynamic SQL.
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]
January 3, 2013 at 7:07 am
RichB (1/3/2013)
How about using Quotename...?SELECT QUOTENAME('o''neil', '''')
Yes, QUOTENAME is my preferred method of dealing with dynamic SQL. Especially since it can also handle brackets.
January 3, 2013 at 7:13 am
BrainDonor (1/3/2013)
I have frequently had to script out Stored Procs that use dynamic SQL, and the joys of altering the number of quotes defies description.Using the 'Generate Scripts' option within SSMS is a useful solution for such situations and a lot of people don't realise that it can be used for such. It will create a script with the correct number of quotes, providing an easy way to script such things for moving to different databases.
I love using the Generate Scripts option within SSMS (in fact I plan on blogging on it shortly). The only drawback is that you have to initially put your code into a stored procedure, function etc in order to script it. Nothing wrong with doing that of course, but if you get someone who is somewhat sloppy and forgets to get rid of the "temporary" code then you could end up with a bit of a mess.
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]
January 3, 2013 at 7:14 am
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? And while we're at it to use #'s to delimit dates/times (ala MS Access)?
Oh, and use a dedicated "escape" character instead of doubling the escaped character? Sheesh!
Sorry, this is one of (many) pet peeves I have with T-SQL.
I agree that single quotes are kind of a pita to deal with but NOTHING from Access be considered in a real RDBMS. The notion of using #'s doesn't work either. That one is used for temp tables. About the only standard character left would be the tilde or the pipe.
I totally agree that there should be something to indicate that the entire following string has been escaped. .NET does that quite well. I think the challenge here is yet again the lack of any unused characters that don't already mean something else.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 3, 2013 at 7:21 am
Agreed, these are the kind of reasons why I replace char(39) with char(96)
Viewing 15 posts - 1 through 15 (of 73 total)
You must be logged in to reply to this topic. Login to reply