Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Single Quotation Marks in SQL Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, January 02, 2013 9:53 PM
 Hall of Fame Group: General Forum Members Last Login: 2 days ago @ 8:22 AM Points: 3,433, Visits: 1,627
 Comments posted to this topic are about the item Single Quotation Marks in 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
Post #1402167
 Posted Wednesday, January 02, 2013 9:59 PM
 Forum Newbie Group: General Forum Members Last Login: Wednesday, December 04, 2013 1:43 AM Points: 2, Visits: 179
 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 03, 2013 2:03 AM
 Forum Newbie Group: General Forum Members Last Login: Sunday, January 06, 2013 11:06 PM Points: 1, Visits: 4
 Answer isDECLARE @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 @topsqlPRINT '-------'EXEC sp_executesql @topsqlFinger Crossed Vandana
Post #1402230
 Posted Thursday, January 03, 2013 2:04 AM
 Forum Newbie Group: General Forum Members Last Login: Tuesday, October 29, 2013 2:32 AM Points: 3, Visits: 98
 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 @topsqlPRINT '-------'EXEC sp_executesql @topsql`Thanks, Hasham Niaz
Post #1402232
 Posted Thursday, January 03, 2013 4:10 AM
 UDP Broadcaster Group: General Forum Members Last Login: 2 days ago @ 8:37 AM Points: 1,467, Visits: 7,453
 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. BrainDonorLinkedinBlog Site
Post #1402282
 Posted Thursday, January 03, 2013 4:14 AM
 Forum Newbie Group: General Forum Members Last Login: Thursday, June 06, 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 03, 2013 4:16 AM
 Ten Centuries Group: General Forum Members Last Login: Thursday, December 05, 2013 3:18 AM Points: 1,036, Visits: 825
 How about using Quotename...?SELECT QUOTENAME('o''neil', '''')
Post #1402284
 Posted Thursday, January 03, 2013 5:14 AM
 Grasshopper Group: General Forum Members Last Login: 2 days ago @ 5:29 PM Points: 18, Visits: 738
 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 03, 2013 6:16 AM
 SSCommitted Group: General Forum Members Last Login: 2 days ago @ 6:21 AM Points: 1,722, Visits: 2,064
 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+2In 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 03, 2013 6:34 AM
 Old Hand Group: General Forum Members Last Login: Monday, December 02, 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

 Permissions