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

Feeds:: Posts; Comments

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

February 17, 2008 by pinaldave

Jr. Developer asked me other day how to escape single quote?

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

UPDATE : A very good SQL blog Author Madhivanan has written similar article but with good example. Recommended read.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Posted in Pinal Dave, SQL, SQL Authority, SQL Error Messages, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | 10 Comments

10 Responses

on April 22, 2008 at 5:13 pm B. Rama Rao

@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
on April 22, 2008 at 5:15 pm B. Rama Rao

write a simple example for me, which meets my requirement.
on February 18, 2009 at 1:34 pm MohammedAshrafali

Thankx
on February 18, 2009 at 4:42 pm kittiphongC

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
on March 17, 2009 at 4:31 am Chris

This is a helpful post.

This is hideous functionality. Completely unintuitive. One more reason for me to hate SQL Server…
on May 20, 2009 at 6:21 pm awanish

How to find central in below string in sql

AWA/Central/_catalogs/masterpage

Thanks in advance
on June 24, 2009 at 2:00 am Martin

Chris, this is standard ANSI SQL behavior, you [removed word].
on June 24, 2009 at 2:05 am Martin

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!
on June 24, 2009 at 4:55 pm Brian Tkatch

@Martin

Please do not use abusive language. It is not helpful to anyone.
on June 24, 2009 at 5:06 pm pinaldave

Thank you Brian Tkatch

For pointing this out. I have edited abusive response.

Kind Regards,
Pinal