Find a string within a field

This may be easy but I am new to this so here it goes. I am trying to find if a string can be found in a mysql database field.

The following will only find exactly match

SELECT mycontact, userid
FROM mytable
WHERE email = #form.email#'

The following will return result if the field email is contain in the form.email

 SELECT mycontact, userid
 FROM mytable
 WHERE email like '%#form.email#%'

But these will not give what I want. I want the reverse of the above. The email field can contain MORE than one email addresses. I want to be able to enter a value i.e. form.email and search the table an开发者_如何学God find any record in the "email" field that contain form.emal. The %LIKE% won't work because I don't want to retrieve more than one. If someone enter cd@gmail.com, I don't want to get abcd@gmail.com, bcd@gmail.com etc. That is the difficult part. Help is appreciated. I am using MYSQL database. Thanks.

You need to redesign your schema, having a list of emails in a column is a very, very bad design and SQL isn't well designed to handle it (as you're running into already). Better would be an email table where each contact can have one or more rows based on the email addresses they provide.

Trust me, it'll work better, and make your life a lot easier. Redesign now.

Try a regular expression for the selection.

If you are storing more than one email in a field you should add a delimiter like a space or a pipe. You can do

SELECT mycontact, userid FROM mytable WHERE email like '%#form.email#%'

then explode the delimiter and loop in array. Compare each email by using pregmatch. But I would recommend you to use a separate table for this as what they said above. I would totally agree with them.