abcs12@yahoo.com,abcs@gmail.com,cityname
cityname,zipcode,abcs@gmail.com
abcs@officedomain.com,cityname,abc@gmail.com
apartment,streetname,cityname,abc@gmail.com,abcd@yahoo.com

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

select concat(substring_index((substring_index(Residence_Address1, '@', 2)),'@gmail',1),'@gmail.com') as mail, residence_address1
 from mytable
where Residence_Address1!="" and Residence_Address1 like '%gmail%'