Web Development & Execution
David Addison
by David Addison
share this
?fl
« Back to the Blog

Escaping percent and underscore characters in t sql like clause

07/21/2010
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:

  • test pattern
  • pattern
  • pattern test
  • test pattern test

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:

  • test pattern 1
  • pattern 1

but not

  • 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:

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

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

Thanks!

Thank you for contacting us!

We'll be in touch!

Back Home ×