iks_in iks_in - 4 months ago 26
SQL Question

Extract email id from a field having multiple words in mysql

I have a table in mysql -mytable.

Column Name:ResidenceAddress1

It has values like this:

sap3200@gmail.com,Rourkela
sap3212@gmail.com 2nd street,7 hils
2nd street, sap3212@gmail.com


I've tried this way:

select (case when substring_index(Residence_Address1, ' ', 1) like '%@%'
then substring_index(Residence_Address1, ' ', 1)
else substring_index(Residence_Address1, ' ', -1)
end) as email, Residence_Address1
from mytable
where Residence_Address1 like '%gmail%' and Email_Personal1=""


But its not giving me only email ids. How do I get only email ids from multiple words?

JPG JPG
Answer

For your sample data, here is a solution:

select 
    concat(trim(substring_index(substring_index(ResidenceAddress1, '@', '1'), ' ', -1)), '@gmail.com') as mail
    ,ResidenceAddress1
from mytable

This answer is based on your email is all gmail.com and your separator is space.

Edited:

substring_index(ResidenceAddress1, '@', '1') will get you this:

sap3200
sap3212
2nd street, sap3212

then

substring_index('sap3200', ' ', -1) => sap3200
substring_index('sap3212', ' ', -1) => sap3212
substring_index('2nd street, sap3212', ' ', -1) => sap3212

Demo Here