iks_in iks_in - 4 months ago 11
SQL Question

Extract email id from field mysql

I have a column First_Name. But it has names along with email ids. eg. 'bobbybob@gmail.com Bobby' and sometimes like this, 'Bobby bobbybob@gmail.com'

I want to fetch email id from first_name column.

I tried this way:

select left(First_Name,locate(' ',First_Name)-1)
from mytable
where First_Name like '%gmail%' and Email_Personal=""


But this works in only first case where email id is first while in second case where name is first, it fetches name. I also want to remove email id after storing it into another column -Email_Personal. So, in other words, I want to fetch email id from first_name and want to store it into email_personal and keep only name in the first_name.

I've also tried this way:

select substring_index(first_name,' ',1)
from mytable
where First_Name like '%gmail%' and Email_Personal1=""


But this also worked for the first case only. Where there is name first then, it returns only name not email id.

Answer

If you always have a 1-word first name, you can do:

select (case when substring_index(first_name, ' ', 1) like '%@%'
             then substring_index(first_name, ' ', -1)
             else substring_index(first_name, ' ', 1)
         end) as RealFirstName

You can use similar logic to extract the email address:

select (case when substring_index(first_name, ' ', 1) like '%@%'
             then substring_index(first_name, ' ', 1)
             else substring_index(first_name, ' ', -1)
         end) as EmailAddress
Comments