SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Kenneth Fisher / Single Quotation Marks in SQL / Latest Posts

RE: Single Quotation Marks in SQL

Kenneth.Fisher — Sun, 13 Jan 2013 16:18:59 GMT

Sorry I'm a little bit late posting my answer to the "homework". Lot's of people had posted correct answers already so I didn't feel all that rushed to post my answer :-).[code="sql"]DECLARE @topsql nvarchar(200)SET @topsql = 'DECLARE @quotedvar nvarchar(100) ' + char(13) + 'DECLARE @sql nvarchar(1000) ' + char(13) + '' + char(13) + 'SET @quotedvar = ''O''''Neil''' + char(13) + '' + char(13) + 'SET @sql = ''PRINT '''''' + REPLACE(@quotedvar,'''''''','''''''''''') + ''''''''' + char(13) + '' + char(13) + 'PRINT @sql' + char(13) + '' + char(13) + 'EXEC sp_executesql @sql' PRINT @topsqlPRINT '-------'EXEC sp_executesql @topsql[/code]The best test for the correct answer is of course to run it and see if the output works :)

RE: Single Quotation Marks in SQL

Kenneth.Fisher — Sun, 13 Jan 2013 16:10:49 GMT

I would tend to agree. Dynamic SQL is a very powerful tool. But it's just one tool of many and should only be used when it's appropriate and with appropriate attention paid to security. For that matter combining parameters and dynamic sql can be particularly powerful.

RE: Single Quotation Marks in SQL

Irozenberg 1347 — Sun, 13 Jan 2013 15:16:03 GMT

IMHO dynamic SQL outside of database is a recepie for a disaster. Anybody hear about SQL injection in last 15+ years? Why not use parameterised stored procedures/user-defined functions. I would use dynamic SQL only for Sql script generation or Within stored procedure (in exceptional cases when nothing Else could work).

RE: Single Quotation Marks in SQL

Dave Vroman — Mon, 07 Jan 2013 23:46:52 GMT

Since our apps are primarily web pages we step around the problem by converting the problem characters to unicode. The single quote character is converted to "'". It no longer is in the way, there is no special coding necessary and it displays properly in a browser.

RE: Single Quotation Marks in SQL

Steven Willis — Sun, 06 Jan 2013 14:22:09 GMT

[quote][b]Eirikur Eiriksson (1/6/2013)[/b][hr]One workaround is to use tokens when writing dynamic SQL statements, especially if nested more than one level;[/quote]That sounds like a great idea. I've never seen the problem dealt with like that before. Thumbs up!

RE: Single Quotation Marks in SQL

Eirikur Eiriksson — Sun, 06 Jan 2013 06:33:42 GMT

Nice article and a classic subject. One workaround is to use tokens when writing dynamic SQL statements, especially if nested more than one level;[code="sql"]DECLARE @TOKEN NVARCHAR(1) = NCHAR(123)DECLARE @SQ NVARCHAR(1) = NCHAR(39)DECLARE @SQLSTR NVARCHAR(MAX) = N' DECLARE @TOKEN NVARCHAR(1) = NCHAR(124) DECLARE @SQ NVARCHAR(1) = NCHAR(39) DECLARE @SQLSTR NVARCHAR(MAX) = N{ DECLARE @TOKEN NVARCHAR(1) = NCHAR(125) DECLARE @SQ NVARCHAR(1) = NCHAR(39) DECLARE @SQLSTR NVARCHAR(MAX) = N| DECLARE @TOKEN NVARCHAR(1) = NCHAR(126) DECLARE @SQ NVARCHAR(1) = NCHAR(39) DECLARE @SQLSTR NVARCHAR(MAX) = N} DECLARE @quotedvar nvarchar(100) = N~O¡Neil~ SELECT @quotedvar = REPLACE(@quotedvar,NCHAR(161),NCHAR(39)) PRINT @quotedvar } PRINT @@NESTLEVEL SELECT @SQLSTR = REPLACE(@SQLSTR,@TOKEN,@SQ) PRINT @SQLSTR EXEC sp_executesql @SQLSTR | PRINT @@NESTLEVEL SELECT @SQLSTR = REPLACE(@SQLSTR,@TOKEN,@SQ) PRINT @SQLSTR EXEC sp_executesql @SQLSTR { PRINT @@NESTLEVEL SELECT @SQLSTR = REPLACE(@SQLSTR,@TOKEN,@SQ) PRINT @SQLSTR EXEC sp_executesql @SQLSTR'PRINT @@NESTLEVELSELECT @SQLSTR = REPLACE(@SQLSTR,@TOKEN,@SQ)PRINT @SQLSTREXEC sp_executesql @SQLSTR[/code]Execution result;[code="plain"]0 DECLARE @TOKEN NVARCHAR(1) = NCHAR(124) DECLARE @SQ NVARCHAR(1) = NCHAR(39) DECLARE @SQLSTR NVARCHAR(MAX) = N' DECLARE @TOKEN NVARCHAR(1) = NCHAR(125) DECLARE @SQ NVARCHAR(1) = NCHAR(39) DECLARE @SQLSTR NVARCHAR(MAX) = N| DECLARE @TOKEN NVARCHAR(1) = NCHAR(126) DECLARE @SQ NVARCHAR(1) = NCHAR(39) DECLARE @SQLSTR NVARCHAR(MAX) = N} DECLARE @quotedvar nvarchar(100) = N~O¡Neil~ SELECT @quotedvar = REPLACE(@quotedvar,NCHAR(161),NCHAR(39)) PRINT @quotedvar } PRINT @@NESTLEVEL SELECT @SQLSTR = REPLACE(@SQLSTR,@TOKEN,@SQ) PRINT @SQLSTR EXEC sp_executesql @SQLSTR | PRINT @@NESTLEVEL SELECT @SQLSTR = REPLACE(@SQLSTR,@TOKEN,@SQ) PRINT @SQLSTR EXEC sp_executesql @SQLSTR ' PRINT @@NESTLEVEL SELECT @SQLSTR = REPLACE(@SQLSTR,@TOKEN,@SQ) PRINT @SQLSTR EXEC sp_executesql @SQLSTR2 DECLARE @TOKEN NVARCHAR(1) = NCHAR(125) DECLARE @SQ NVARCHAR(1) = NCHAR(39) DECLARE @SQLSTR NVARCHAR(MAX) = N' DECLARE @TOKEN NVARCHAR(1) = NCHAR(126) DECLARE @SQ NVARCHAR(1) = NCHAR(39) DECLARE @SQLSTR NVARCHAR(MAX) = N} DECLARE @quotedvar nvarchar(100) = N~O¡Neil~ SELECT @quotedvar = REPLACE(@quotedvar,NCHAR(161),NCHAR(39)) PRINT @quotedvar } PRINT @@NESTLEVEL SELECT @SQLSTR = REPLACE(@SQLSTR,@TOKEN,@SQ) PRINT @SQLSTR EXEC sp_executesql @SQLSTR ' PRINT @@NESTLEVEL SELECT @SQLSTR = REPLACE(@SQLSTR,@TOKEN,@SQ) PRINT @SQLSTR EXEC sp_executesql @SQLSTR 4 DECLARE @TOKEN NVARCHAR(1) = NCHAR(126) DECLARE @SQ NVARCHAR(1) = NCHAR(39) DECLARE @SQLSTR NVARCHAR(MAX) = N' DECLARE @quotedvar nvarchar(100) = N~O¡Neil~ SELECT @quotedvar = REPLACE(@quotedvar,NCHAR(161),NCHAR(39)) PRINT @quotedvar ' PRINT @@NESTLEVEL SELECT @SQLSTR = REPLACE(@SQLSTR,@TOKEN,@SQ) PRINT @SQLSTR EXEC sp_executesql @SQLSTR 6 DECLARE @quotedvar nvarchar(100) = N'O¡Neil' SELECT @quotedvar = REPLACE(@quotedvar,NCHAR(161),NCHAR(39)) PRINT @quotedvar O'Neil[/code]Eirikur

RE: Single Quotation Marks in SQL

Steven Willis — Fri, 04 Jan 2013 10:36:19 GMT

[quote][b]sknox (1/4/2013)[/b][hr][quote][b]Steven Willis (1/4/2013)[/b][hr]For anyone who really cares about proper typography [url=http://www.amazon.com/The-Chicago-Manual-Style-Publishers/dp/0226103897][u]The Chicago Manual of Style[/u][/url] is the editor's Bible. [/quote]Oh. I thought the Chicago Manual of Style was about pinstripes and fedoras. Thanks for clearing that up! :-P[/quote]Maybe you were confused like Bela Oxmyx... [url=http://www.imdb.com/title/tt0708412/][u]A Piece of the Action[/u][/url]. :laugh:

RE: Single Quotation Marks in SQL

sknox — Fri, 04 Jan 2013 10:19:59 GMT

[quote][b]Steven Willis (1/4/2013)[/b][hr]For anyone who really cares about proper typography [url=http://www.amazon.com/The-Chicago-Manual-Style-Publishers/dp/0226103897][u]The Chicago Manual of Style[/u][/url] is the editor's Bible. [/quote]Oh. I thought the Chicago Manual of Style was about pinstripes and fedoras. Thanks for clearing that up! :-P

RE: Single Quotation Marks in SQL

Steven Willis — Fri, 04 Jan 2013 09:58:52 GMT

The standard ASCII character set is a holdover from the early days of teletype, band printers, computers and manual typewriters. (How many of you have ever seen a band printer? They were incredibly fast.)If one wants to get nitpicky, [i]typographers[/i] have always used the form of characters that was added to the standard ASCII to create the Extended Character set. Even in the days of lead type, most font sets--whether manual or automated linotype--included these typographical characters.[font="Times New Roman"][size="5"]From the standard character set:This is an apostrophe: ' (ASCII 39) This is a quotation mark: " (ASCII 34)From the extended characters set:This is a left single quotation mark: ‘ (ASCII 145)This is a right single quotation mark: ’ (ASCII 146)This is a left double quotation mark: “ (ASCII 147)This is a right double quotation mark: ” (ASCII 148)[/size][/font]ASCII 39 and ASCII 34--in typography--would be used to represent minutes and seconds when displaying latitude or longitude or as an abbreviation for feet and inches.Oh, and since I used the double-hyphen above rather than a true em dash that is cause to differentiate there as well.[font="Times New Roman"][size="5"]This is a hyphen: - (ASCII 45) used as a minus symbol, for compound words, or to break a word between lines.This is an en dash: – (ASCII 150) used to represent a range such as 1952–1987.This is an em dash: — (ASCII 151) used as a text separator. This symbol would properly be used in place of the double-hyphen—like this—rather than the double-hyphen typical of computers and typewriters.And one final typographical note: the ellipsis is typically typed using three periods. But the proper method to display an ellipsis would be to use ASCII char 133: …[/size][/font]For anyone who really cares about proper typography [url=http://www.amazon.com/The-Chicago-Manual-Style-Publishers/dp/0226103897][u]The Chicago Manual of Style[/u][/url] is the editor's Bible.

RE: Single Quotation Marks in SQL

Sean Lange — Thu, 03 Jan 2013 15:18:14 GMT

At the very least the WWW consortium does not agree that there is no such thing. ‘ is left single quote’ is right single quote--EDIT--Proof...I typed in the html escape sequence and they rendered on the page.& lsquo;& rsquo;

RE: Single Quotation Marks in SQL

webrunner — Thu, 03 Jan 2013 15:11:57 GMT

[quote][b]Nemeaux (1/3/2013)[/b][hr]Although the overall technical correctness of this article is good there is a glaring error in the narrative. There is no such thing as a "single quote" mark. There is an apostrophe mark and there is a quotation mark. A single quote looks thusly " and a double quote looks this way "". What the article’s author refers to as a "single quote" is in reality an apostrophe mark ' and what is referred to as a "double quote" is actually a quotation mark. I bring this up because the difference is really quite significant. Calling an apostrophe a "single quote" is, putting it simply, quite wrong. It would be like calling a “V” a single “W” and calling a “W” a “double W”. When looking up "quotation marks" in the Oxford Online Dictionary, http://oxforddictionaries.com/words/punctuation, I see no punctuation mark labeled as "single quote". Nor is there a mention of a "double quote".Yes, I'm tilting windmills. But one must try occasionally, mustn't one?Thanks for the soapbox.[/quote]For your consideration:[url=http://english.stackexchange.com/questions/36046/apostrophe-vs-single-quote]http://english.stackexchange.com/questions/36046/apostrophe-vs-single-quote[/url]Also here:[url=http://en.wikipedia.org/wiki/Quotation_mark]http://en.wikipedia.org/wiki/Quotation_mark[/url]SQL uses what seems to be called typewriter quotation marks, or dumb or straight quotes. I think there is a semantic difference between apostrophes and single quotation marks. For example, many Penguin books use single quotation marks to set off dialogue. In that case, though, the "open quote" is in one direction and the "close quote" is in the inverted direction. So although you may be technically correct (I'm not yet sure that you are), it seems to be the case -- excepting the symmetrical straight or dumb quotes -- that while all apostrophes look like single (close) quotation marks, not all single quotation marks look like apostrophes. See also:[url=http://www.ergonis.com/products/tips/punctuation-apostrophes-quotation-marks.php]http://www.ergonis.com/products/tips/punctuation-apostrophes-quotation-marks.php[/url]That typographical distinction doesn't exist in SQL, which uses a "dumb" quotation mark (for lack of a better term) rather than a "smart" one. So you may indeed be titling at windmills, although I happen to find those same windmills very interesting and am grateful that you contributed your comments to this discussion.- webrunner

RE: Single Quotation Marks in SQL

aleksandr.milman — Thu, 03 Jan 2013 12:34:38 GMT

What about something simpler:declare @q nvarchar(50), @sql nvarchar(100)set @q = '''o''''neil'''set @sql = 'print ' + @q + ''print @sqlEXEC sp_executesql @sql___________________________________print 'o''neil'o'neil

RE: Single Quotation Marks in SQL

Patel.darsan — Thu, 03 Jan 2013 09:16:27 GMT

nice article. thanks.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

RE: Single Quotation Marks in SQL

Kenneth.Fisher — Thu, 03 Jan 2013 09:10:17 GMT

[quote][b]sknox (1/3/2013)[/b][hr][quote][b]roger.plowman (1/3/2013)[/b][hr]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?[/quote]So that when you have a string which contains both delimiters as literals, you have double the problem? Syntactically, having two delimiters that mean the same thing is a problem, not a solution.[quote]And while we're at it to use #'s to delimit dates/times (ala MS Access)?[/quote]"ala MS Access" == NO. Besides, there's plenty of good documentation on date formatting in SQL, and simply changing 's to #s wouldn't give you any better conversion rate.[quote]Oh, and use a dedicated "escape" character instead of doubling the escaped character? Sheesh![/quote]On this one, I agree with you -- in principle at least. From a syntax standpoint, it makes sense to have a specific escape character, like regular expressions do, for example. But what happens when you need to include the escape character as a literal? Double the escaped character, again. In the case of T-SQL and string literals, the only character which would ever need to be escaped is '. So the options were:a) create an escape character (let's say \) and then use that character to escape every literal ' and \ (i.e, have possible two escape clauses: \' and \\), orb) use ' as the escape character for itself and have one possible escape clause ('').So they again opted for the simpler solution.[/quote]I do see one benefit for using an escape character that isn't your delimiter. '\\' is quite a bit easier to follow than ''''. Or for that mater '\\\\' is much easier than ''''''. On the whole though I agree with you. A character had to be picked as an escape character and regardless of what character it was we were going to have some level of problems with it.

RE: Single Quotation Marks in SQL

sknox — Thu, 03 Jan 2013 08:58:29 GMT

[quote][b]roger.plowman (1/3/2013)[/b][hr]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?[/quote]So that when you have a string which contains both delimiters as literals, you have double the problem? Syntactically, having two delimiters that mean the same thing is a problem, not a solution.[quote]And while we're at it to use #'s to delimit dates/times (ala MS Access)?[/quote]"ala MS Access" == NO. Besides, there's plenty of good documentation on date formatting in SQL, and simply changing 's to #s wouldn't give you any better conversion rate.[quote]Oh, and use a dedicated "escape" character instead of doubling the escaped character? Sheesh![/quote]On this one, I agree with you -- in principle at least. From a syntax standpoint, it makes sense to have a specific escape character, like regular expressions do, for example. But what happens when you need to include the escape character as a literal? Double the escaped character, again. In the case of T-SQL and string literals, the only character which would ever need to be escaped is '. So the options were:a) create an escape character (let's say \) and then use that character to escape every literal ' and \ (i.e, have possible two escape clauses: \' and \\), orb) use ' as the escape character for itself and have one possible escape clause ('').So they again opted for the simpler solution.

RE: Single Quotation Marks in SQL

Nemeaux — Thu, 03 Jan 2013 08:44:49 GMT

Ah, if only we could now get BOL to see the light!In any case, good article...keep up the good writing!Dave

RE: Single Quotation Marks in SQL

Kenneth.Fisher — Thu, 03 Jan 2013 08:32:17 GMT

RE: Single Quotation Marks in SQL

Nemeaux — Thu, 03 Jan 2013 08:18:36 GMT

Although the overall technical correctness of this article is good there is a glaring error in the narrative. There is no such thing as a "single quote" mark. There is an apostrophe mark and there is a quotation mark. A single quote looks thusly " and a double quote looks this way "". What the article’s author refers to as a "single quote" is in reality an apostrophe mark ' and what is referred to as a "double quote" is actually a quotation mark. I bring this up because the difference is really quite significant. Calling an apostrophe a "single quote" is, putting it simply, quite wrong. It would be like calling a “V” a single “W” and calling a “W” a “double W”. When looking up "quotation marks" in the Oxford Online Dictionary, http://oxforddictionaries.com/words/punctuation, I see no punctuation mark labeled as "single quote". Nor is there a mention of a "double quote".Yes, I'm tilting windmills. But one must try occasionally, mustn't one?Thanks for the soapbox.

RE: Single Quotation Marks in SQL

james elmer — Thu, 03 Jan 2013 08:04:08 GMT

[quote][b]Kenneth.Fisher (1/3/2013)[/b][hr][quote][b]RichB (1/3/2013)[/b][hr]How about using Quotename...?SELECT QUOTENAME('o''neil', '''')[/quote]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.[/quote]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.

RE: Single Quotation Marks in SQL

Kenneth.Fisher — Thu, 03 Jan 2013 08:00:59 GMT

[quote][b]pop022 (1/3/2013)[/b][hr]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[/quote]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.

RE: Single Quotation Marks in SQL

pop022 — Thu, 03 Jan 2013 07:53:29 GMT

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

RE: Single Quotation Marks in SQL

Kenneth.Fisher — Thu, 03 Jan 2013 07:36:35 GMT

[quote][b]RichB (1/3/2013)[/b][hr]How about using Quotename...?SELECT QUOTENAME('o''neil', '''')[/quote]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.

RE: Single Quotation Marks in SQL

james elmer — Thu, 03 Jan 2013 07:34:34 GMT

[quote][b]Michael R. OBrien Jr (1/2/2013)[/b][hr]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[/quote]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):[code="sql"]SET @sql = 'Print ' + CHAR(39) + REPLACE(@quotedvar, CHAR(39), CHAR(39) + CHAR(39)) + CHAR(39)[/code]

RE: Single Quotation Marks in SQL

Boreades — Thu, 03 Jan 2013 07:21:06 GMT

Agreed, these are the kind of reasons why I replace char(39) with char(96)

RE: Single Quotation Marks in SQL

Sean Lange — Thu, 03 Jan 2013 07:14:45 GMT

RE: Single Quotation Marks in SQL

Kenneth.Fisher — Thu, 03 Jan 2013 07:13:20 GMT

[quote][b]BrainDonor (1/3/2013)[/b][hr]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.[/quote]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.

RE: Single Quotation Marks in SQL

Ryan.Polk — Thu, 03 Jan 2013 07:07:22 GMT

[quote][b]RichB (1/3/2013)[/b][hr]How about using Quotename...?SELECT QUOTENAME('o''neil', '''')[/quote]Yes, QUOTENAME is my preferred method of dealing with dynamic SQL. Especially since it can also handle brackets.

RE: Single Quotation Marks in SQL

Kenneth.Fisher — Thu, 03 Jan 2013 07:03:26 GMT

[quote][b]MG-148046 (1/3/2013)[/b][hr][quote][b]johnbrown105 56149 (1/3/2013)[/b][hr][quote][b]Michael R. OBrien Jr (1/2/2013)[/b][hr]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[/quote]+1[/quote]+2In addition, I try to use Powershell Here-Strings wherever I can instead of dynamic SQL.[/quote]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[code="sql"]REPLACE(@quotedvar, CHAR(39), CHAR(39)+CHAR(39))[/code]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.

RE: Single Quotation Marks in SQL

roger.plowman — Thu, 03 Jan 2013 06:34:38 GMT

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.

RE: Single Quotation Marks in SQL

MG-148046 — Thu, 03 Jan 2013 06:16:20 GMT

[quote][b]johnbrown105 56149 (1/3/2013)[/b][hr][quote][b]Michael R. OBrien Jr (1/2/2013)[/b][hr]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[/quote]+1[/quote]+2In addition, I try to use Powershell Here-Strings wherever I can instead of dynamic SQL.

RE: Single Quotation Marks in SQL

johnbrown105 56149 — Thu, 03 Jan 2013 05:14:23 GMT

RE: Single Quotation Marks in SQL

RichB — Thu, 03 Jan 2013 04:16:00 GMT

How about using Quotename...?SELECT QUOTENAME('o''neil', '''')

RE: Single Quotation Marks in SQL

Boreades — Thu, 03 Jan 2013 04:14:58 GMT

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.

RE: Single Quotation Marks in SQL

BrainDonor — Thu, 03 Jan 2013 04:10:25 GMT

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.

RE: Single Quotation Marks in SQL

hbn_100 — Thu, 03 Jan 2013 02:04:49 GMT

Following code will do the trick;[code="sql"]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[/code]Thanks, Hasham Niaz

RE: Single Quotation Marks in SQL

vandana.goyal — Thu, 03 Jan 2013 02:03:07 GMT

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

RE: Single Quotation Marks in SQL

Michael R. OBrien Jr — Wed, 02 Jan 2013 21:59:08 GMT

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

Single Quotation Marks in SQL

Kenneth.Fisher — Wed, 02 Jan 2013 21:53:11 GMT

Comments posted to this topic are about the item [B]Single Quotation Marks in SQL[/B]