Little SQL Server Tricks: Use Underline Characters With Like

I needed to find all columns with an underscore (_) in their name and came up with this query:

1
2
3
4
5
6
7
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME like '%_%'
ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME

But instead of showing me columns with a _ in their name, I got every column back that exists in my database. When I replace _ with anything else, I got the expected result. What is so special with the _ character?

As it turns out, the _ is a wildcard to match one character in SQL Server – as it is in Oracle. To get the result I wanted, I had to escape the _ character and rewrite my where-clause to this:

1
WHERE COLUMN_NAME like '%\_%' {escape '\'}

If you are used to regular expressions, you may find this alternative useful:

1
WHERE COLUMN_NAME like '%[_]%'

With this small change, I finally got the result I was expecting.