iks_in iks_in - 4 months ago 12
MySQL Question

Extract emails from a field having multiple words in mysql

In my MySQL table, I've a column-Residence_Address1. But it has other columns' values also in it. I want to extract email ids from the field.

Sample data.

eg.,

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


If I tried this way:

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


1) It merges data like this. Output:
abcs12@gmail.com


instead of abcs@gmail.com

2)It fetches email along with address.Output:
apartment,streetname,cityname,abc@gmail.com

Instead of abc@gmail.com from a row.

I tried this way.

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%'


1) It didn't merge the record like yahoo id with gmail id.

2) But it gives the full address along with email id.

eg.streetname,cityname,gmailid.

Above attempts were mainly fetching gmail ids.Instead of fetching only gmail id, I want to extract all emails from a row. Only emails.

JPG JPG
Answer

Try this:

SELECT 
    CONCAT(
        TRIM(
            SUBSTRING_INDEX(
                SUBSTRING_INDEX(
                    SUBSTRING_INDEX(ResidenceAddress1, '@gmail', '1')
                , ' ', -1)
            , ',', -1)
        )
        , '@gmail.com') AS mail
    ,ResidenceAddress1
FROM mytable

Schema:

CREATE TABLE mytable
    (`ResidenceAddress1` varchar(58))
;

INSERT INTO mytable
    (`ResidenceAddress1`)
VALUES
    ('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')
;

Result:

| mail           | ResidenceAddress1                                          |
+----------------+------------------------------------------------------------+
| abcs@gmail.com | abcs12@yahoo.com,abcs@gmail.com,cityname                   | 
| abcs@gmail.com | cityname,zipcode,abcs@gmail.com                            | 
| abc@gmail.com  | abcs@officedomain.com,cityname,abc@gmail.com               | 
| abc@gmail.com  | apartment,streetname,cityname,abc@gmail.com,abcd@yahoo.com | 

Edited:

SELECT 
    CONCAT(
        TRIM(
            SUBSTRING_INDEX(
                SUBSTRING_INDEX(
                    SUBSTRING_INDEX(
                         IF(LOCATE('@yahoo', ResidenceAddress1), ResidenceAddress1, ''),
                         '@yahoo', 1)
                , ' ', -1)
            , ',', -1)
        )
        , IF(LOCATE('@yahoo', ResidenceAddress1), '@yahoo', '')
        , SUBSTRING_INDEX(SUBSTRING_INDEX(IF(LOCATE('@yahoo', ResidenceAddress1), ResidenceAddress1, ''), '@yahoo', -1), ',', 1)
    ) AS mail
    ,ResidenceAddress1
FROM mytable

Demo

Comments