Little SQL Server Tricks: Escape _ in LIKE Queries

If you search for a value with an _ in the name, you learn something new about SQL Server, but end up with a result that does not match your expectation:

SELECT * FROM [dbo].[DatabaseLog]
WHERE Object LIKE '%_%'

If we run this query, we do not get back everything with an _ in it. Instead, we get back everything. How is that possible? The _ is a placeholder that stands for any character, what gives us back every row that has any character anywhere in the column we search for. That is most likely not what we try to do.

If we want to use the _ not as a placeholder but as an underscore, we need to escape it. We can do it with this syntax:

SELECT * FROM [dbo].[DatabaseLog]
WHERE Object LIKE '%\_%' ESCAPE '\'

We tell SQL Server that we use the \ as an escape marker and that lets it search for the underscore we are interested in. With this slight modification of our query, we get all rows containing a value with an underscore. Keep that in mind should you search for an _ and end up with different results.