SQL SELECT query not working in mysql

Question

I have an sql query:

SELECT * FROM presentations_db.presenters_tbl WHERE e-mail LIKE '%@%';

It does not seem to work and I don't know why. If I remove the WHERE part it works, but includes all records. I only want records that have an e-mail address. I am using mysql workbench. The e-mail field is a VARCHAR(45). It seems so simple but not... There is no feedback or error messages. The result list doesn't even open.

e-mail field exists and is spelt correctly. presentations_db.presenters_tbl exists and is spelt correctly. — Jacques MALAPRADE
– Jacques MALAPRADE, Commented Apr 10, 2013 at 14:41
The fact that it works without the WHERE part tells me there must be a problem with the e-mail field, but what?? — Jacques MALAPRADE
– Jacques MALAPRADE, Commented Apr 10, 2013 at 14:42
If possible, I'd recommend changing your column name to e_mail - DB objects whose name contain special characters are always terrible to work with (this also applies to umlauts / accents / case-sensitive names). — Frank Schmitt
– Frank Schmitt, Commented Apr 11, 2013 at 7:06

John Woo · Accepted Answer · 2013-04-10 14:47:42Z

3

you must wrap the column name with backtick to avoid syntax error.

SELECT * FROM presentations_db.presenters_tbl WHERE `e-mail` LIKE '%@%

if you don't wrap it with backtick, it will perform artihmetic operation:

mail is subtracted from e

causing you to receive this error message: Unknown column 'e' in 'where clause'

SQLFiddle Demo

edited Apr 10, 2013 at 14:47

answered Apr 10, 2013 at 14:40

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

Comments

echo_Me · Accepted Answer · 2013-04-10 14:42:29Z

0

you query is right just replace this

    e-mail

by this

   `e-mail`

answered Apr 10, 2013 at 14:42

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

Comments

Mike Lischke · Accepted Answer · 2013-04-11 06:53:31Z

0

Apart from the required quoting with back-ticks or double quotes (if ANSI quoting is enabled) I'd change the where clause this:

SELECT * FROM presentations_db.presenters_tbl WHERE `e-mail` IS NOT NULL;

since all you want is the records which have an e-mail set. It doesn't check for validity of the address, however.

answered Apr 11, 2013 at 6:53

Mike Lischke

54.3k18 gold badges142 silver badges209 bronze badges

Collectives™ on Stack Overflow

SQL SELECT query not working in mysql

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related