Mysql Equal String returns 0 Rows and LIKE String returns 1 Row

Question

Good evening! I have a MYSQL problem that I can't figure out...

Here's my situation:

Table Name: up_categorys

ID | Category
--------------
5  | Xbox Live

When I do this query, the database returns zero rows:

SELECT id FROM up_categorys WHERE category_name = 'Xbox Live' LIMIT 0 , 30

But when I do this query, the database returns 1 row:

SELECT id FROM up_categorys WHERE category_name LIKE '%Xbox Live%' LIMIT 0 , 30

Maybe a genius can solve this? Maybe you!

Are you sure Row ID 5 is exactly 'Xbox Live' and doesn't have any spaces or anything before or after? — Mike Christensen
– Mike Christensen, Commented Jul 2, 2013 at 22:13
What about SELECT id FROM up_categorys WHERE category_name LIKE 'Xbox Live' LIMIT 0 , 30 or SELECT id FROM up_categorys WHERE category_name LIKE 'Xbox Live%' LIMIT 0 , 30 or SELECT id FROM up_categorys WHERE category_name LIKE '%Xbox Live' LIMIT 0 , 30. Does any of these queries return something? — Federico Nafria
– Federico Nafria, Commented Jul 2, 2013 at 22:13
can you try to SELECT id FROM up_categorys WHERE TRIM(category_name) = 'Xbox Live'? what do you get? — fthiella
– fthiella, Commented Jul 2, 2013 at 22:17
I'd also try SELECT length(category_name) FROM up_categorys WHERE category_name LIKE '%Xbox Live%' to make sure the value has the expected 9 characters. — Mike Christensen
– Mike Christensen, Commented Jul 2, 2013 at 22:17

Martin M · Accepted Answer · 2013-07-02 22:15:01Z

2

You might have leading or trailing blanks in the category_name field.
Try

SELECT id FROM up_categorys WHERE TRIM(category_name) = 'Xbox Live' LIMIT 0 , 30

answered Jul 2, 2013 at 22:15

Martin M

8,6883 gold badges39 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

The problem was indeed the trailing blanks. Thanks for saving me 2 hours of my life ;-)!