VHayxmkQ

1abcs12@yahoo.com,abcs@gmail.com,cityname
2cityname,zipcode,abcs@gmail.com
3abcs@officedomain.com,cityname,abc@gmail.com
4apartment,streetname,cityname,abc@gmail.com,abcd@yahoo.com
5	
6select 
7concat(trim(substring_index(substring_index(ResidenceAddress1, '@', '1'), ' ', -1)), '@gmail.com') as mail
8,ResidenceAddress1
9from mytable
10	
11select concat(substring_index((substring_index(Residence_Address1, '@', 2)),'@gmail',1),'@gmail.com') as mail, residence_address1
12 from mytable
13where Residence_Address1!="" and Residence_Address1 like '%gmail%'