T-SQL Escape Character

In T-SQL, I frequently use the LIKE clause when searching for a string in a VARCHAR or NVARCHAR column. For example, suppose I wanted to search a table that contains user comments for all strings that contain the word ‘cheque’ somewhere within the comment. I would simply use the following SELECT statement:

SELECT *
FROM dtComment C
WHERE C.Comment LIKE ‘%cheque%’

This would return all rows that contain the word ‘cheque’ anywhere within the comment.

Suppose I wanted to return all rows that contain the word ‘cheque’ followed by something in square brackets (i.e. ‘[' and ']‘). It turns out that the square brackets are reserved in T-SQL LIKE clauses to allow searching for a single character within a range of characters (See the LIKE description in MSDN) . The square brackets are used , so you cannot simply write :

SELECT *
FROM dtComment C
WHERE C.Comment LIKE ‘%cheque [%’

You need to escape the square bracket so that the LIKE clause actually searches for the character ‘[‘. The syntax for escaping the square bracket is the following:

SELECT *
FROM dtComment C
WHERE C.Comment LIKE ‘%cheque ![%’ ESCAPE ‘!’

Here, the ! is the escape character, and we use the ESCAPE keyword to tell T-SQL to interpret literally the character immediately following the escape character. So the above SELECT statement is essentially saying “Literally interpret the character directly following the exclamation point.”

 

 

Advertisement