SQL SELECT query not working with Email Addresses

Question

I am trying to query an e-mail address stored in my database for log-in. I am having issues with the query in PHP, when I attempted the query with SQL in PHPMyAdmin it returns an empty set. After doing some testing I determined the following for an email of [email protected]:

Works:

SELECT * FROM `Careers` WHERE `Email` LIKE '%something%' and

SELECT * FROM `Careers` WHERE `Email` LIKE '%gmail.com%' and

SELECT * FROM `Careers` WHERE `Email` LIKE '%@%'.

Doesn't work:

SELECT * FROM `Careers` WHERE `Email` LIKE '[email protected]' and

SELECT * FROM `Careers` WHERE `Email` LIKE '%[email protected]%' and

SELECT * FROM `Careers` WHERE `Email` LIKE '%@gmail.com%'

SELECT * FROM `Careers` WHERE `Email` LIKE '%something%gmail.com'

I'm completely lost as to how to correct this. The only think I can think of is it is an issue with the @ sign as when I add the @ sign the query seems to fail. Any help you could provide would be greatly appreciated!

Answer 1

Are you sure that it's not working. See a proof here that it works http://sqlfiddle.com/#!9/26b00/4. But you should change your queries a bit as shown below

SELECT * FROM table1 WHERE `Email` = '[email protected]' -- No need of LIKE operator

SELECT * FROM table1 WHERE `Email` = '[email protected]' -- No need of LIKE operator

SELECT * FROM table1 WHERE `Email` LIKE '%@gmail.com' -- search before string

SELECT * FROM table1 WHERE `Email` LIKE 'something_gmail.com' -- search a single char

EDIT:

Per your latest comment your collation armscii8_general_ci is the issue here. For example create the table like

CREATE TABLE Table1
    (`email` varchar(19) collate armscii8_general_ci)
;

INSERT INTO Table1
    (`email`)
VALUES
    ('[email protected]')
;

Do a select * ... returns below; as you can see the . as turned to © kind of copyright symbol and that's why the wildcard with LIKE operator not working.

something@gmail©com

Change your query to use _ wilcard with LIKE operator to match any single character and it will work fine. See http://sqlfiddle.com/#!9/ec46f/8

SELECT * FROM Table1 WHERE `Email` LIKE 'something@gmail_com'; 

Answer 2

Use the Regex force! Just a where statement to solve this:

select * from site_form
where email REGEXP '^[A-Za-z0-9._%\-+!#$&/=?^|~]+@[A-Za-z0-9.-]+[.][A-Za-z]+$';