Escape Character in SQL Server

Question

I want to use quotation with escape character. How can I do?

I have received error in SQL Server

Unclosed quotation mark after the character string.

I'm writing SQL query in a varchar variable but I have received that error:

Unclosed quotation mark after the character string.

I want to use quotation mark as an escape char.

Possible duplicate of [How do I escape a single quote in SQL Server?](https://stackoverflow.com/questions/1586560/how-do-i-escape-a-single-quote-in-sql-server) — Jens Schauder, Jan 22 '18 at 16:34

score 125 · Answer 1 · answered Feb 28 '11 at 08:52

125

You can escape quotation like this:

select 'it''s escaped'

result will be

it's escaped

answered Feb 28 '11 at 08:52

dugokontov

3,752
1
21
25

This should be the answer. – Tony Dec 23 '19 at 15:38

score 96 · Accepted Answer · edited Apr 07 '20 at 13:13

96

To escape ' you simly need to put another before: ''

As the second answer shows it's possible to escape single quote like this:

select 'it''s escaped'

result will be

it's escaped

If you're concatenating SQL into a VARCHAR to execute (i.e. dynamic SQL), then I'd recommend parameterising the SQL. This has the benefit of helping guard against SQL injection plus means you don't have to worry about escaping quotes like this (which you do by doubling up the quotes).

e.g. instead of doing

DECLARE @SQL NVARCHAR(1000)
SET @SQL = 'SELECT * FROM MyTable WHERE Field1 = ''AAA'''
EXECUTE(@SQL)

try this:

DECLARE @SQL NVARCHAR(1000)
SET @SQL = 'SELECT * FROM MyTable WHERE Field1 = @Field1'
EXECUTE sp_executesql @SQL, N'@Field1 VARCHAR(10)', 'AAA'

edited Apr 07 '20 at 13:13

Revious

6,994
29
89
133

answered Feb 28 '11 at 08:52

AdaTheDev

128,935
26
185
187

30

Why is this the accepted answer? It doesn't answer the question. – Peter Moore Feb 05 '18 at 13:11
5

@PeterMoore Either the OP would have used the 1st part of my answer (doubling up the quotes, as per other answers below), or would have used the preferred approach I recommended for building up a SQL query in a string variable - to use parameterized SQL. Either way, both are answers to the question – AdaTheDev Feb 05 '18 at 14:48
It doesn't answer the question. Sometimes user need ODBC connection which means you can only use pure SQL. – Tony Dec 23 '19 at 15:38
Edited answer to more it more clear and better fit the question – Revious Apr 07 '20 at 13:14

score 52 · Answer 3 · edited Jan 25 '13 at 09:32

52

You can define your escape character, but you can only use it with a LIKE clause.

Example:

SELECT columns FROM table
WHERE column LIKE '%\%%' ESCAPE '\'

Here it will search for % in whole string and this is how one can use ESCAPE identifier in SQL Server.

edited Jan 25 '13 at 09:32

Ivan Ferić

4,545
11
34
46

answered Jan 25 '13 at 09:13

Aniket A

521
4
2

Seph · Answer 4 · 2015-08-19T12:22:35.103

22

You need to just replace ' with '' inside your string

SELECT colA, colB, colC
FROM tableD
WHERE colA = 'John''s Mobile'

You can also use REPLACE(@name, '''', '''''') if generating the SQL dynamically

If you want to escape inside a like statement then you need to use the ESCAPE syntax

It's also worth mentioning that you're leaving yourself open to SQL injection attacks if you don't consider it. More info at Google or: http://it.toolbox.com/wiki/index.php/How_do_I_escape_single_quotes_in_SQL_queries%3F

edited Aug 19 '15 at 12:22

answered Feb 28 '11 at 08:52

Seph

7,864
10
58
86

and yet answers by dugokontov or RichardPianka don't have any similar -1? – Seph Sep 21 '14 at 12:16
@MichaelMunsey try it for yourself: `select '` returns the error `Unclosed quotation mark after the character string ''`. Nowhere in my answer do I use `"` only two `'`, not sure why mine is the only answer with down votes. – Seph Aug 19 '15 at 12:23

score 12 · Answer 5 · answered Feb 28 '11 at 08:47

12

Escaping quotes in MSSQL is done by a double quote, so a '' or a "" will produce one escaped ' and ", respectively.

answered Feb 28 '11 at 08:47

Richard Pianka

3,187
2
22
33

score 2 · Answer 6 · answered Jun 06 '20 at 13:24

2

If you want to escape user input in a variable you can do like below within SQL

  Set @userinput = replace(@userinput,'''','''''')

The @userinput will be now escaped with an extra single quote for every occurance of a quote

answered Jun 06 '20 at 13:24

Nayajiv

21
2

score 0 · Answer 7 · answered Nov 01 '19 at 07:24

0

You could use the **\** character before the value you want to escape e.g insert into msglog(recipient) values('Mr. O\'riely') select * from msglog where recipient = 'Mr. O\'riely'

answered Nov 01 '19 at 07:24

paradox earthling

1
1

score 0 · Answer 8 · answered Jul 08 '20 at 16:33

0

WHERE username LIKE '%[_]d';            -- @Lasse solution
WHERE username LIKE '%$_d' ESCAPE '$';
WHERE username LIKE '%^_d' ESCAPE '^';

FROM: SQL Server Escape an Underscore

answered Jul 08 '20 at 16:33

R.Alonso

750
1
7
8

score -2 · Answer 9 · answered May 28 '19 at 08:26

-2

To keep the code easy to read, you can use square brackets [] to quote the string containing ' or vice versa .

answered May 28 '19 at 08:26

Ben

866
10
26

This is incorrect. Brackets work on illegal characters in field, table, or schema names. – Jamie Marshall Jul 15 '19 at 23:14
Yeah, you right, its for the object names, not string contents. I must read the question wrong. – Ben Jul 16 '19 at 00:57

Escape Character in SQL Server

9 Answers9

Linked