mysql select query - to match one field with the other field

I am trying to find records that has the following scenario.

ID |     name   |      email
 1      Robert        robert@gmail.com
 2      William       bill@gmail.com
 3      Michael       michael@gmail.com
 4      Micahel       mike@gmail.com

Based on the above table, I want to find the records where the "name" is contained in the "email field", here record 1 and 3 should be the output and not 2 and 4. Is there any way I can do this comparison?

I tried reading about regex but couldn't find anythin开发者_如何学运维g. If it's comparison of same value, it will be straightforward, but I am not having any clue for this one. I thought of LIKE but looks like this cannot have field names.

The exact syntax will depend on how you want to define the relationship.

Are you looking for the name anywhere in the email address? (This will be slow)

select id,name,email
from your_table
where email like concat('%',name,'%')

Just at the beginning of the email address?

select id,name,email
from your_table
where email like concat(name,'%')

Just before the @ sign?

select id,name,email
from your_table
where email like concat(name,'@%')

You can use LIKE, you just have to use it in combination with CONCAT.

SELECT
    ID,
    name,
    email
FROM
    yourTable
WHERE
    email LIKE CONCAT(name, '%');

The CONCAT will return a string which can be used to match against email via LIKE.

This should work

SELECT * FROM table WHERE email LIKE (CONCAT('%',name,'%'))

select * from your_table where lower(substring_index(email,'@',1))=lower(name)