Escaping percent and underscore characters in t sql like clause

In T-SQL we normally use a like clause to search for patterns. The following:

select *
from table
where column like '%pattern%'

will return all the rows of the table which contain <pattern> in them. The percent sign is used as the placeholder for any number of characters in the string.

The above query will match any of the below strings:

• testpattern
• pattern
• patterntest
• testpatterntest

Similarly, the underscore character will match any single character of the string. The following:

select *
from table
where column like '%pattern_'

will match only the strings with a single character following the pattern, such as:

• testpattern1
• pattern1

but not

• testpattern1test

But, if you are searching for the actual underscore or percent character within a string you should use the T-SQL escape sequence [%] or [_].

If you want to find the string test_string%, then your T-SQL like clause would look like the following:

select *
from table
where column like 'test[_]string[%]'

P.S. If you want to match [ character, then you would use [[] syntaxis.