CODEWITHSUNDEEP
mysql
Liam
Liam

Reputation: 41

MySQL query LIKE %...% query returning other results?

So I'm trying to code a PHP script, but we'll just leave it at the SQL part of things since this is where the issue is arising. I've a SELECT * query which should only grab from the rows where the user ID matches, and the badge ID meets their userID followed by an underscore. Although, it's grabbing results that shouldn't be included?

Here's my SQL query:

SELECT * 
FROM  `user_badges` 
WHERE  `user_id` = 1
AND  `badge_id` LIKE  '%1_%'

That should only return badges that start/contain 1_, it is grabbing all the badges that do contain/start with 1_ but it's also grabbing it215. If I search a different user ID, for example my own, it will grab all the badges with 3_ AND it's also grabbing ACH_RoomDecoFurniCount31 which is confusing because it doesn't contain 3_. Maybe there's more to it? Could someone please point me in the right direction.

Upvotes: 0

Views: 162

Answers (4)

Darshan Mehta
Darshan Mehta

Reputation: 30819

You need to escape the _ as it's a wildcard character. Your query would should be like this:

SELECT * 
FROM  `user_badges` 
WHERE  `user_id` = 1
AND  `badge_id` LIKE  '%1\_%'

Upvotes: 1

GavinF
GavinF

Reputation: 397

_ is a wildcard "_ matches exactly one character." so what you are saying is: % : starts with anything(or nothing) 1: contains 1 _: has exactly 1 of % (or anything, or nothing)

http://dev.mysql.com/doc/refman/5.7/en/string-comparison-functions.html

Upvotes: 0

Thilo
Thilo

Reputation: 262504

_ is also a wildcard character. It means "any single character" (whereas % is "any sequence of characters").

You could escape/quote that _ or use the LOCATE function instead of a pattern match.

WHERE badge_id LIKE  '%1\_%'

WHERE locate('1_', badge_id) > 0

Upvotes: 0

DenisS
DenisS

Reputation: 1687

_ is also a wildcard in SQL - A substitute for a single character

Upvotes: 0

Related Questions