Select all rows that have '_' in a given column

Question

I want to select the rows that have a '_' character in a given column from SQL. when I try to run this query, it returns all the rows in the table

SELECT * FROM [Attribute] where LibID=26 and Name like '%_%'

but the following query yields what I need

SELECT * FROM [Attribute] where LibID=26 and CHARINDEX('_', Name) > 0

why my first query is not returning expected result. Maybe I am missing some information/knowledge of SQL.

Answer 1

The _ (underscore) character is used by the LIKE function to match any character. If you want to use it as an underscore, you need to escape it:

SELECT * 
FROM [Attribute] 
where LibID=26 
and Name like '%[_]%'

Answer 2

The underscore is a wildcard character in a LIKE query. It indicates any single character.

To use the LIKE syntax, use:

SELECT * FROM [Attribute] where LibID=26 and Name like '%[_]%'