mysql doesn't find data without wildcard

Question

I'm running a simple select in phpmyadmin. The query is:

SELECT email FROM users
WHERE users.email LIKE 'user1@email.com'
OR  users.email LIKE 'user2@email.com'
OR  users.email LIKE 'user3@email.com'

This query return an empty set. On the other hand, the same query with wildcards:

SELECT email FROM users
WHERE users.email LIKE 'user1@email.com%'
OR  users.email LIKE 'user2@email.com%'
OR  users.email LIKE 'user3@email.com%'

returns

user1@email.com
user2@email.com
user3@email.com

I don't know why the two queries don't return the same results. Maybe there is an unprintable character or white space in the field? Is the query without the wildcard just plain wrong? If it is an unprintable character or white space, how do I tell what character it is? I've tried 'user1@email.com ' to no avail. I realize it would be simple enough to set everything to its trimmed value, but I'm curious as to what the problem is.

Answer 1

If the question is not "how can I get this query to work even when there are unprintable characters?" but instead, "how can I figure out what unprintable characters are breaking my query?" you could try the following:

SELECT REPLACE(HEX('user1@email.com '), HEX('user1@email.com'), '') FROM users
WHERE users.email LIKE 'user1@email.com%';

If the character in question is a simple space, the query above should return 20, indicating that the difference between your search string and the actual value is a space. It would actually not indicate if the difference were at the start or end of the database value, but given the other properties of the search, this is safe to assume for your purposes.

Answer 2

According to the docs: 'The pattern need not be a literal string'.

If you want to match an exact value. You can use this sql sentence:

SELECT email FROM users
WHERE users.email in('user1@email.com', 'user2@email.com', 'user3@email.com'); 

or if you still want to use a wildcard:

SELECT email, length(email) FROM users
WHERE users.email LIKE '%user1@email.com%'
OR  users.email LIKE '%user2@email.com%'
OR  users.email LIKE '%user3@email.com%' 

Answer 3

I think there could be some trailing character (often a space or a newline).

You can try concatenating some control strings for a check:

SELECT concat(">>", email , "<<") FROM users
WHERE users.email LIKE 'user1@email.com%'
OR  users.email LIKE 'user2@email.com%'
OR  users.email LIKE 'user3@email.com%'

or more easily you can return the email's length:

SELECT email, length(email) FROM users
WHERE users.email LIKE 'user1@email.com%'
OR  users.email LIKE 'user2@email.com%'
OR  users.email LIKE 'user3@email.com%'