Single Quotation Marks in SQL

  • Kenneth.Fisher

    SSCoach

    Points: 19637

    Comments posted to this topic are about the item Single Quotation Marks in SQL

    Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

  • Michael R. OBrien Jr

    Valued Member

    Points: 62

    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

  • vandana.goyal

    SSC Journeyman

    Points: 83

    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

  • hbn_100

    Valued Member

    Points: 65

    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

  • BrainDonor

    SSCoach

    Points: 19209

    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.

    Steve Hall
    Linkedin
    Blog Site

  • Boreades

    Old Hand

    Points: 335

    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.

  • RichB

    SSCrazy Eights

    Points: 9651

    How about using Quotename...?

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

  • johnbrown105 56149

    SSC-Addicted

    Points: 493

    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

  • MG-148046

    SSCrazy Eights

    Points: 9942

    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.

  • roger.plowman

    SSChampion

    Points: 10184

    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.

  • Kenneth.Fisher

    SSCoach

    Points: 19637

    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 was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

  • Ryan.Polk

    Old Hand

    Points: 373

    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

    SSCoach

    Points: 19637

    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 was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

  • Sean Lange

    SSC Guru

    Points: 286491

    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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Boreades

    Old Hand

    Points: 335

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

Viewing 15 posts - 1 through 15 (of 74 total)

You must be logged in to reply to this topic. Login to reply