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

Single Quotation Marks in SQL Expand / Collapse
Author
Message
Posted Wednesday, January 2, 2013 9:53 PM


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: Monday, October 27, 2014 1:09 PM
Points: 3,467, Visits: 1,821
Comments posted to this topic are about the item Single Quotation Marks in 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 #1402167
Posted Wednesday, January 2, 2013 9:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:16 AM
Points: 2, Visits: 206
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
Post #1402168
Posted Thursday, January 3, 2013 2:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, January 6, 2013 11:06 PM
Points: 1, Visits: 4
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
Post #1402230
Posted Thursday, January 3, 2013 2:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 25, 2014 12:01 PM
Points: 3, Visits: 117
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

Post #1402232
Posted Thursday, January 3, 2013 4:10 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 4:29 AM
Points: 1,471, Visits: 8,397
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.


BrainDonor
Linkedin
Blog Site
Post #1402282
Posted Thursday, January 3, 2013 4:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 6, 2013 5:04 AM
Points: 9, Visits: 45
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.



Post #1402283
Posted Thursday, January 3, 2013 4:16 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 8:38 AM
Points: 1,070, Visits: 908
How about using Quotename...?

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




Post #1402284
Posted Thursday, January 3, 2013 5:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 7:28 AM
Points: 18, Visits: 846
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
Post #1402302
Posted Thursday, January 3, 2013 6:16 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:38 AM
Points: 1,830, Visits: 2,211
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.

Post #1402327
Posted Thursday, January 3, 2013 6:34 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, December 2, 2013 6:30 AM
Points: 346, Visits: 691
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.

Post #1402336
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse