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»»»

Single Quotation Marks in SQL Expand / Collapse
Author
Message
Posted Thursday, January 03, 2013 7:03 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: Yesterday @ 11:53 AM
Points: 3,436, Visits: 1,683
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 Fisher
I 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
Post #1402351
Posted Thursday, January 03, 2013 7:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 8:01 AM
Points: 28, Visits: 258
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.
Post #1402354
Posted Thursday, January 03, 2013 7:13 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: Yesterday @ 11:53 AM
Points: 3,436, Visits: 1,683
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 Fisher
I 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
Post #1402359
Posted Thursday, January 03, 2013 7:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:44 PM
Points: 11,970, Visits: 10,995
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 Moden's 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)
Post #1402360
Posted Thursday, January 03, 2013 7:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 06, 2013 5:04 AM
Points: 9, Visits: 45
Agreed, these are the kind of reasons why I replace char(39) with char(96)


Post #1402364
Posted Thursday, January 03, 2013 7:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 10, 2014 9:15 AM
Points: 42, Visits: 46
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)

Post #1402375
Posted Thursday, January 03, 2013 7:36 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: Yesterday @ 11:53 AM
Points: 3,436, Visits: 1,683
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 Fisher
I 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
Post #1402377
Posted Thursday, January 03, 2013 7:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 07, 2014 9:33 AM
Points: 16, Visits: 180
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



Post #1402394
Posted Thursday, January 03, 2013 8:00 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: Yesterday @ 11:53 AM
Points: 3,436, Visits: 1,683
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 Fisher
I 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
Post #1402400
Posted Thursday, January 03, 2013 8:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 10, 2014 9:15 AM
Points: 42, Visits: 46
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.
Post #1402402
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse