Single Quotation Marks in SQL

  • fregatepllada

    SSCommitted

    Points: 1648

    FYI QUOTENAME function Return Types nvarchar(258), so be aware

  • odecar

    Valued Member

    Points: 62

    Yep and the returned string includes single quotes so no need to include additional quotes before tbe plus sign

  • Eirikur Eiriksson

    SSC Guru

    Points: 182359

    For fun and to further on my previous post on simplifying nested string handling, here is another method which uses a pass-through parameter for sp_executesql.

    ๐Ÿ˜Ž

    DECLARE @ONELL NVARCHAR(50) = N'O''Nell';

    DECLARE @PARAM NVARCHAR(100) = N'@ONELL NVARCHAR(50)';

    PRINT @@NESTLEVEL;

    PRINT @ONELL;

    DECLARE @SQL_STR NVARCHAR(MAX) = N'

    PRINT @@NESTLEVEL

    PRINT @ONELL

    DECLARE @PARAM NVARCHAR(100) = N''@ONELL NVARCHAR(50)'';

    DECLARE @SQL_STR NVARCHAR(MAX) = N''

    PRINT @@NESTLEVEL

    PRINT @ONELL

    DECLARE @PARAM NVARCHAR(100) = N''''@ONELL NVARCHAR(50)'''';

    DECLARE @SQL_STR NVARCHAR(MAX) = N''''

    PRINT @@NESTLEVEL

    PRINT @ONELL

    PRINT @@NESTLEVEL

    PRINT @ONELL

    ''''

    EXEC SP_EXECUTESQL @SQL_STR,@PARAM,@ONELL;

    ''

    EXEC SP_EXECUTESQL @SQL_STR,@PARAM,@ONELL;

    '

    EXEC SP_EXECUTESQL @SQL_STR,@PARAM,@ONELL;

    Results

    0

    O'Nell

    2

    O'Nell

    4

    O'Nell

    6

    O'Nell

    6

    O'Nell

  • annlcarey

    SSC Rookie

    Points: 48

    Agreed. Rather than trying to keep track of the quotes, I use the tilde ~ to represent my single quote, then do a replace at the end.

  • Phil Parkin

    SSC Guru

    Points: 243763

    annlcarey (7/14/2014)


    Agreed. Rather than trying to keep track of the quotes, I use the tilde ~ to represent my single quote, then do a replace at the end.

    Careful with that. The tilde is a reserved character - maybe it could cause you issues somehow, somewhere:

    http://msdn.microsoft.com/en-us/library/ms173468(v=sql.110).aspx

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • jshahan

    SSCarpal Tunnel

    Points: 4622

    1. The outside 2 single quotes delimit the string.

    2. On the inside of the string you must have 2 single quotes for each single quote you are representing.

    I am most appreciative of this article if for no other reason than these two pointers. I rarely write dynamic sql from scratch (usually fixing it) and when I do I typically struggle with the single quotes. I was able to quickly complete the "homework" just by applying these two rules.

    They are now part of my notes and I thank you for that.

  • jshahan

    SSCarpal Tunnel

    Points: 4622

    PS: Iโ€™m surprised Celko hasnโ€™t jumped on this thread screeching for even broaching the subject of dynamic sql much less enabling it. I hope heโ€™s okay.:-)

  • Kenneth.Fisher

    SSCoach

    Points: 19587

    jshahan (7/14/2014)


    1. The outside 2 single quotes delimit the string.

    2. On the inside of the string you must have 2 single quotes for each single quote you are representing.

    I am most appreciative of this article if for no other reason than these two pointers. I rarely write dynamic sql from scratch (usually fixing it) and when I do I typically struggle with the single quotes. I was able to quickly complete the "homework" just by applying these two rules.

    They are now part of my notes and I thank you for that.

    Thanks! That makes the whole thing worth while. dynamic SQL can be a bear if you don't work with it much and no matter how many times people say "Dynamic SQL is bad" you still have to deal with it in legacy code.

    You might also look some blog posts I wrote:

    http://sqlstudies.com/2013/06/12/best-practice-recommendations-for-writing-dynamic-sql/

    http://sqlstudies.com/2013/07/01/writing-dynamic-sql-a-how-to/

    http://sqlstudies.com/2013/07/22/generic-dynamic-sql-stored-procedure/

    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]

  • bpriddy

    SSC Veteran

    Points: 265

    This is a very good brain teaser as well as a good test for someone to show they know SQL very well.

    Awesome article. Looking for your answer now. ๐Ÿ™‚

    Thanks Kenneth Fisher,

    Britt

  • rstone

    SSCertifiable

    Points: 6011

    I would have thought that the option specify an escape character or a literal string would have been added years ago. I guess it's not that important in the bigger scheme of things.

    Randy
    Helpdesk: Perhaps Im not the only one that does not know what you are doing. ๐Ÿ˜‰

  • Kenneth.Fisher

    SSCoach

    Points: 19587

    Let's not forget that this is also about the ability to read dynamic SQL. And it doesn't matter if you like or dislike dynamic SQL at some point you are going to have to be able to read it.

    Here is a simple example of generating a series of commands to print out the name of a database.

    declare @sql nvarchar(max)

    SET @sql = N'SELECT ''PRINT ''''''+name+''''''''' +

    N' FROM sys.databases'

    EXEC sp_executesql @sql

    Take a minute and count up the number of 's. Or better yet try to write it yourself. And remember that this is a SIMPLE example.

    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]

  • wechandler

    SSC Rookie

    Points: 34

    It's interesting to see how people differ in their preference on this. For me CHAR(39) has been my preference for a long time.

    Nice article though.

  • gregwjohnston

    Valued Member

    Points: 58

    Dynamic SQL is a necessary, lazy throwback. And if Microsoft had any self respect they would kill it unceremoniously this instant in favor of ANYTHING resembling useful.

  • J-440512

    SSCertifiable

    Points: 6285

    Not I hope, by breaking all existing code in production - this they did when they killed basic to introduce dot nyet basic.

Viewing 14 posts - 61 through 74 (of 74 total)

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