Escaping percent and underscore characters in t sql like clause07/21/2010
In T-SQL we normally use a like clause to search for patterns. The following:
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:
- test pattern
- pattern test
- test pattern test
Similarly, the underscore character will match any single character of the string. The following:
where column like '%pattern_'
will match only the strings with a single character following the pattern, such as:
- test pattern 1
- pattern 1
- test pattern 1test
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:
where column like 'test[_]string[%]'
P.S. If you want to match [ character, then you would use [ syntaxis.