CODEWITHSUNDEEP
sql-serverescapingchar
esquare
esquare

Reputation: 4285

Escape Character in SQL Server

I want to use quotation with escape character. How can I do to avoid the following error when one has a special character?

Unclosed quotation mark after the character string.

Upvotes: 167

Views: 743595

Answers (9)

R.Alonso
R.Alonso

Reputation: 1075

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

FROM: SQL Server Escape an Underscore

Upvotes: 4

Nayajiv
Nayajiv

Reputation: 41

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

Upvotes: 4

AdaTheDev
AdaTheDev

Reputation: 147344

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'

Upvotes: 146

paradox earthling
paradox earthling

Reputation: 91

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'

Upvotes: -1

Ben
Ben

Reputation: 1301

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

Upvotes: -1

Seph
Seph

Reputation: 8703

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

Upvotes: 26

Aniket A
Aniket A

Reputation: 671

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.

Upvotes: 67

dugokontov
dugokontov

Reputation: 4622

You can escape quotation like this:

select 'it''s escaped'

result will be

it's escaped

Upvotes: 198

Richard Pianka
Richard Pianka

Reputation: 3366

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

Upvotes: 15

Related Questions