SQL SERVER – How to Escape Single Quotes – Fix: Error: 105 Unclosed quotation mark after the character string

Jr. A developer asked me the other day how to escape single quote? I find this question very interesting.

SQL SERVER - How to Escape Single Quotes - Fix: Error: 105 Unclosed quotation mark after the character string singledouble

User can escape single quote using two single quotes (NOT double quote).

Following T-SQL Script will give error

USE AdventureWorks
GO
SELECT *
FROM Person.Address
WHERE City = ‘Villeneuve-d’Ascq’
GO

Resultset:

Error: 105 Unclosed quotation mark after the character string ‘

To fix the error mentioned above, escape single quotes with two single quotes as displayed in following example.

USE AdventureWorks
GO
SELECT *
FROM Person.Address
WHERE City = 'Villeneuve-d''Ascq'
GO

Well, it is a pretty easy way to escape single quotes. Here are a few interesting quotes about single and double quotes. In fiction, at least with American English (AmE), there’s really no use for single quotation marks other than as a quote within another quote. In general, quotation marks are used to mark direct speech in a text, to indicate irony, or to highlight the title of works that are part of a larger whole — like chapters of a novel, an article in a magazine or newspaper.

Let me know what you think of this blog post by leaving your opinion in the comment sections.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Error Messages, SQL Scripts, SQL Server, SQL String
Previous Post
SQL SERVER – Introduction to Aggregate Functions
Next Post
SQL SERVER – Popular Articles of SQLAuthority Blog

Related Posts

74 Comments. Leave new

  • @ENTITY_NAME NVARCHAR(100)

    IF @ENTITY_NAME = ‘%’
    BEGIN
    SELECT @WHERE = @WHERE + ‘ AND (T.ENTITY_ID LIKE ”’ + @ENTITY_NAME + ”’ OR T.ENTITY_ID IS NULL)’
    END

    (If @ENTITY_NAME value is ‘ramarao’ in this I did not get any problem. But, ‘ramarao’s ‘ I am getting error like Unclosed quotation mark after the character string ‘). Can you please help out me by giving proper code (how to replace two single quotes in place of one single quote).

    Thanks in Advance.

    Thanks,
    B. Rama Rao

    Reply
    • Problem is mother of solution…
      Here’s one of the solution to pass single quote (‘) a.k.a. Apostrophe in searches as variables.
      Just add the following to your T-SQL

      Declare @find nvarchar(5)
      Declare @replace nvarchar(5)
      Declare @yourvariable –this is the variable used in search

      Set @find = ”+ char(39) +” — these are two single quotes
      Set @replace = ”+char(39)+char(39)+”
      Set @yourvariable = replace(@yourvariable,@find,@replace) –this will replace one single quote with two double quote

      This will be helpful in statement such as select * from tbl_name where column_name like ‘%+@yourvariable+%’

      Hope this helps and saves some development time.
      bhattji

      Reply
    • I think what the OP is looking for, is something like the code below.

      I had the same issue when I imported some Countries, one of which was Cote d’lvoire. But is was misspelled in Import file as Cote d’lovire. So I tried this code, but obviously would not work:

      UPDATE [dbo].[Country_Codes_ISO]
      SET Country = ‘Cote d’lvoire’
      WHERE Country = ‘Cote d’lovire’

      So what you need to do is use the CHAR function, and the ASCII Code for a Single quote is 39. So using this code below works:

      UPDATE [dbo].[Country_Codes_ISO]
      SET Country = ‘Cote d’ + CHAR(39) + ‘lvoire’
      WHERE Country = ‘Cote d’ + CHAR(39) + + ‘lovire’

      Reply
  • write a simple example for me, which meets my requirement.

    Reply
  • MohammedAshrafali
    February 18, 2009 1:34 pm

    Thankx

    Reply
  • OK this is easiest example
    My value that I want to query is -> Ma’ry (you will see it has ‘ among word)

    select Name
    from employee
    where Name = ‘Ma”ry’

    word by word -> Single quote+Ma+Single quote+Single quote+ry+Single quote

    Reply
  • This is a helpful post.

    This is hideous functionality. Completely unintuitive. One more reason for me to hate SQL Server…

    Reply
  • How to find central in below string in sql

    AWA/Central/_catalogs/masterpage

    Thanks in advance

    Reply
  • Chris, this is standard ANSI SQL behavior, you [removed word].

    Reply
  • Oh, and for you other [removed word]:

    set quoted_identifier off
    select “Hello ‘you’ if that’s your real name”
    set quoted identifier on

    Learn how to learn!

    Reply
    • Martin,

      How’s it feel up on your ivory tower?

      Thanks for sharing your solution for the we, your inferiors. It’s a good solution and just saved me some time.

      But WTF do you mean by scolding us rubes with “Learn how to learn?” Are you going to tell us that you’ve never posted a perfectly good question on one forum or another?

      One way to learn is to ask. That’s what the OP was doing.

      Reply
  • Brian Tkatch
    June 24, 2009 4:55 pm

    @Martin

    Please do not use abusive language. It is not helpful to anyone.

    Reply
  • Thank you Brian Tkatch

    For pointing this out. I have edited abusive response.

    Kind Regards,
    Pinal

    Reply
  • Hello, I have a set of columns I need use in a query but they contain single quote elements.

    e.g.: [Mark’s House]
    Whenever I try to do this:

    SET @sql_query = ‘INSERT INTO ‘ + @TABLE_XYZ + ‘ ( ‘ + @TRG_FIELDS + ‘ ) VALUES ( ‘ + @COLUMN_ABC ‘ + ‘)’ ;
    EXEC ( @sql_query );

    The query works as long as the columns do not contain the single quote element.

    Can anyone give me hand on this?
    I am no longer sure of what could be the solution to this.

    I am sure it’s simple. :(

    Reply
  • Brian Tkatch
    July 21, 2009 6:38 pm

    @ed.

    Either escape the single-quote with another, or encase it in brackets.

    VALUES ([ ‘ + @COLUMN_ABC + ‘])’’;

    Reply
  • Thanks Brian. I am lost with the double quote at the end of the line. This is the query as I have it:

    SET @sql_query = ‘INSERT INTO ‘ + @TRG__db_table + ‘ ( ‘ + @TRG__tb_fields + ‘ ) VALUES ( ‘ + @user_name + ‘, ‘ + @TRG__address + ‘, ‘ + @TRG__user_name + ‘ , ‘ + @TRG__city_town + ‘, ‘ + @TRG__state_province + ‘ )’;

    PRINT ‘SQL-Query: ‘ + @sql_query;

    Should I make it like:

    SET @sql_query = ‘INSERT INTO [‘ + @TRG__db_table + ‘] ( [‘ + @TRG__tb_fields + ‘] ) VALUES ( [‘ + @user_name + ‘], [‘ + @TRG__address + ‘], [‘ + @TRG__user_name + ‘], [‘ + @TRG__city_town + ‘], [‘ + @TRG__state_province + ‘] )’;

    If this is ok, where does the double quote should be placed?

    Reply
  • I have tried your suggestion and this is the result:

    SET @sql_query = “SELECT COUNT( loc_name + ctv_name + xyz_code ) FROM [dbo].[‘ + @country + ‘_Listings] WHERE ( loc_name = [‘ + @province + ‘] ) AND ( ctv_name = [‘ + @city + ‘] ) AND ( sic_code = [‘ + @xyz_code + ‘] )”;

    Msg 103, Level 15, State 4, Procedure set_RECORDS, Line 54
    The identifier that starts with ‘

    SELECT COUNT( loc_name + ctv_name + xyz_code ) FROM [dbo].[‘ + @country + ‘_Listings]’

    is too long. Maximum length is 128.

    Reply
  • Brian Tkatch
    July 22, 2009 5:57 pm

    @ed.

    Sorry about that. The double-quote should be a single-quote. It needs to end off the ])

    In your last example too, make the double-quote a single-quote.

    Double-quote are used to quote names, such as a TABLE or COLUMN. Single-quotes are used for values.

    Reply
  • Brian Tkatch
    July 22, 2009 9:01 pm

    @ed.

    I made a mistake. Brackets are for objects, not literals.

    So, escape it with a single-quote. REPLACE(col, ””, ”””)

    SET @sql_query = ‘SELECT COUNT(loc_name + ctv_name + xyz_code )’
    + ‘ FROM [dbo].[‘ + @country + ‘_Listings]’
    + ‘ WHERE loc_name = ‘ + REPLACE(@province, ””, ”””)
    + ‘ AND ctv_name = ‘ + REPLACE(@city, ””, ”””)
    + ‘ AND sic_code = ‘ + REPLACE(@xyz_code, ””, ”””);

    The confusion here is that you are using dynamic SQL. If you use parameters instead, all these problems go away.

    Obviously, you want dynamic SQL because of the TABLE which is dynamic. But is that really what you want? Why not have one Listing TABLE with country as a COLUMN inside it?

    Reply
  • We have a DataSet with Table Adapters built for MSAccess using OLEDB. We are using Visual Studio 2005 and .Net 2.0.

    When the designer creates insert, update and delete queries it encloses the table name and field names with a ` (ASCII char 96).
    For example:

    INSERT INTO `table` (`col1`, `col2`, `col3`) values (….)

    Is there a reason for this character? Assuming it’s for escape purposes, is there a setting to make Visual Studio use another character, eg “[” or “]”?

    Reply
  • Our main goal is to use the same SQL queries (basic inserts, updates, deletes) in both MS Access and SQL Server.
    Our app was originally written for Access using the VS designer to generate the queries.

    After testing these queries, we found that if the ` char was removed and replaced by [ ] brackets, then all the queries would work in both db’s.

    Reply
  • I know this is an old post, but I’d like to thank you because that’s just what I needed

    Reply
  • Following dbcc statement work fine from QA, but it failing using in a sql job.

    Please help…..

    Use “EPO_USLAX-EPO01”
    go
    DBCC SHRINKFILE (“ePO_USLAX-EPO01”, 20)
    GO
    DBCC SHRINKFILE (“ePO_USLAX-EPO01_log”, 10)
    go

    Reply

Leave a Reply