CODEWITHSUNDEEP
sql-serversql
Prashant Cholachagudda
Prashant Cholachagudda

Reputation: 13092

Strange behavior of sql server

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??

Upvotes: 0

Views: 524

Answers (4)

Guffa
Guffa

Reputation: 700192

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'

Upvotes: 0

Blorgbeard
Blorgbeard

Reputation: 103447

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.

Upvotes: 5

sisve
sisve

Reputation: 19781

LIKE operator

Quickie: _ is a wildcard character that matches anything.

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

Upvotes: 2

j3r03nq
j3r03nq

Reputation: 196

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

Upvotes: 0

Related Questions