Escape Character in SQL Server

问题:

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 am 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 escape char.

Thanks in advance

回答1:

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'

回答2:

You can escape quotation like this:

select 'it''s escaped'

result will be

it's escaped

回答3:

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.

回答4:

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

回答5:

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