Single Quotation Marks in SQL

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

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

  • 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

  • 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.

  • 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

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • 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.

  • 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.:-)

  • 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]

  • 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

  • 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.

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

  • 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]

  • 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.

  • 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.

  • 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 73 (of 73 total)

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