Escape Character – MS SQL

What is the escape character in SQL?

Assume you have to find the the names that contains single quote (‘), in this scenario single quote(‘) itself works as the escape character. 

SELECT *
FROM Person.Person
WHERE LastName LIKE '%''%'

Consider the situation where you are tying to find records which contains [ or % itself, what are our options?

Okay, the  ESCAPE keyword can be used to specify an escape character of our choice. 

SELECT *
FROM #temp
WHERE Column1 LIKE '%\%' ESCAPE ('\')

In another case square brackets are also used in the pattern matching, So with out an escape character it will be tough to find [.

Its very simple query with a escape keyword

SELECT *
FROM #temp
WHERE Column1 LIKE '%^[%' ESCAPE ('^')

 You can download the sample code used in the article here

References
LIKE (Transact-SQL)

Leave a Reply

Your email address will not be published. Required fields are marked *