Tuesday, July 9, 2013

T-SQL: Underscores must be escaped in a like statement

Quick description:  a like statement with a string containing an underscore will return strange results.


This is just a quick note on an issue that caused me some pain today, when looking for a string not like '%string_%' , I was excluding everything containing the string plus subsequent characters.

This means that while I want to see Not like '%string_%' return values like 'string2' but not 'string_2', what I actually get is nothing.


Underscores have to be escaped in like statements.  The correct syntax is Not like '%string[_]%', this will return values like 'string2' but not 'string_2' 

For more information see Using Wildcard Characters As Literals (like)