SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Single Quotation Marks in SQL


Single Quotation Marks in SQL

Author
Message
Kenneth Fisher
Kenneth Fisher
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6375 Visits: 2060
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 strive to live in a world where a chicken can cross the road without being questioned about its motives.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/Link to my Blog Post --> www.SQLStudies.com
Ryan.Polk
Ryan.Polk
SSC-Enthusiastic
SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)

Group: General Forum Members
Points: 187 Visits: 574
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.
Kenneth Fisher
Kenneth Fisher
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6375 Visits: 2060
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 strive to live in a world where a chicken can cross the road without being questioned about its motives.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/Link to my Blog Post --> www.SQLStudies.com
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63021 Visits: 17959
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Boreades
Boreades
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 61
Agreed, these are the kind of reasons why I replace char(39) with char(96)



jelmer@minitab.com
jelmer@minitab.com
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 56
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


That works and I think makes things a little cleaner, but in this example with dynamic sql you would still need to do this (replace one char(39) in O'Neil with two):

SET @sql = 'Print ' + CHAR(39) + REPLACE(@quotedvar, CHAR(39), CHAR(39) + CHAR(39)) + CHAR(39)


Kenneth Fisher
Kenneth Fisher
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6375 Visits: 2060
RichB (1/3/2013)
How about using Quotename...?

SELECT QUOTENAME('o''neil', '''')



I looked at Quotename when I was writing this but honestly I'm still not sure exactly what it does. I tried it out in this context and couldn't get it to do what I needed it to. I may just have been using it wrong though.

Kenneth FisherI strive to live in a world where a chicken can cross the road without being questioned about its motives.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/Link to my Blog Post --> www.SQLStudies.com
pop022
pop022
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 382
I know the point you were trying to get across, but using sp_executesql is not needed to print the value of the variable. It could simply be written as follows.

DECLARE @quotedvar nvarchar(100)

SET @quotedvar = 'O''Neil'

PRINT @quotedvar



Kenneth Fisher
Kenneth Fisher
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6375 Visits: 2060
pop022 (1/3/2013)
I know the point you were trying to get across, but using sp_executesql is not needed to print the value of the variable. It could simply be written as follows.

DECLARE @quotedvar nvarchar(100)

SET @quotedvar = 'O''Neil'

PRINT @quotedvar


Absolutely, but it did make for a simple example of the use of dynamic sql. In fact using sp_executesql is also just an example as you can use EXEC in my examples just as easily.

Kenneth FisherI strive to live in a world where a chicken can cross the road without being questioned about its motives.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/Link to my Blog Post --> www.SQLStudies.com
jelmer@minitab.com
jelmer@minitab.com
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 56
Kenneth.Fisher (1/3/2013)
RichB (1/3/2013)
How about using Quotename...?

SELECT QUOTENAME('o''neil', '''')



I looked at Quotename when I was writing this but honestly I'm still not sure exactly what it does. I tried it out in this context and couldn't get it to do what I needed it to. I may just have been using it wrong though.


I agree. Quotename is fine to use in creating a valid sql string for O'Neil, however it doesn't help you (that I can see) in creating an executable string using sp_executesql. All Quotename is trying to do is to create a valid sql string, it does not know you want the command PRINT + a valid sql string all stored as a sql string.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search