SQL Server: How to Treat an Underscore Literally

Data and Databases
1

I’m having trouble getting SQL Server to recognize an underscore as a standard character instead of a wildcard.

I need to write a query that filters rows where a column value ends with an underscore followed by a specific letter. The typical LIKE operator misinterprets the underscore as a placeholder for any single character. How can I modify my query to search for the literal underscore character?

For example, consider this adjusted code snippet:

SELECT *
FROM UserProfiles
WHERE ProfileName LIKE '%|_b' ESCAPE '|';

Any insight on properly escaping the underscore in such cases would be really helpful.

2

Based on experience, an effective solution was to use SQL Server’s pattern matching to treat the underscore as a literal character by enclosing it in square brackets. For instance, rewriting the pattern as ‘%[_]b’ works reliably. This method provides clarity to both the SQL parser and anyone reviewing the code by explicitly indicating that the underscore should be interpreted literally rather than as a wildcard. Although the ESCAPE clause is also valid, the bracket approach simplifies understanding and maintenance in many cases where only a few special characters need escaping.

3

hey, try using a different approach: if the value always ends with that exact 2-character combo, use the right() function. e.g. WHERE right(ProfileName,2) = ‘_b’ — it’s a neat workaround and sidesteps escapin issues.