Strange behavior of sql server

Question

Hey guys,

I have following table structure in my DB

ID               Name
--------------------------
ID_1             Name1

ID1              Name2

when I execute the following query

SELECT * FROM tblNames
WHERE ID LIKE '_1'

I get both records selected...

any suggestions how to get rid off it??

Answer 1

SELECT * FROM tblNames
WHERE ID LIKE '[_]1'

Answer 2

LIKE operator

Quickie: _ is a wildcard character that matches anything.

SELECT * FROM tblNames
WHERE ID LIKE '\_1' ESCAPE '\'

Answer 3

An underscore (_) is a special character in LIKE expressions that matches a single character.

To match an actual underscore, you need to escape it:

select * from tblNames where id like '%\_1' escape '\'

Here I'm telling SQL Server to consider backslash an escape character, and using it to escape the underscore.

Alternatively, you can represent the underscore as a character range with a single character in it - it will be interpreted literally in this case:

select * from tblNames where id like '%[_]1'

Which is a bit more succinct.

Reference: MSDN.

Answer 4

The underscore character is a wildcard for the like operator, and it matches any one character. In your case the pattern '_1' matches both '_1' and 'D1' in the data.

To use a literal underscore character in a pattern you need to put brackets around it so that it becomes a character range containing only one character:

SELECT * FROM tblNames
WHERE ID LIKE '[_]1'