escaping – Escape Character in SQL Server

escaping – Escape Character in SQL Server

You can escape quotation like this:

select its escaped

result will be

its escaped

To escape you simly need to put another before:

As the second answer shows its possible to escape single quote like this:

select its escaped

result will be

its escaped

If youre concatenating SQL into a VARCHAR to execute (i.e. dynamic SQL), then Id recommend parameterising the SQL. This has the benefit of helping guard against SQL injection plus means you dont 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, [email protected] VARCHAR(10), AAA

escaping – Escape Character in SQL Server

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.

Leave a Reply

Your email address will not be published.