Extract email address from mysql field

Question

I have a longtext column "description" in my table that sometimes contains an email address. I need to extract this email address and add to a separate column for each row. Is this possible to do in MySQL?

Answer 1

You can use substring index to capture email addresses...

The first substring index capture the account.
The second substring_index captures the hostname. It is necessary to pick the same email address in case the are multiple atso (@) stored in the column.

select concat( substring_index(substring_index(description,'@',1),' ',-1)
             , substring_index(substring_index( description,
                                                substring_index(description,'@',1),-1),
                               ' ',1))

Answer 2

Yes, you can use mysql's REGEXP (perhaps this is new to version 5 and 8 which may be after this question was posted.)

SELECT *, REGEXP_SUBSTR(`description`, '([a-zA-Z0-9._%+\-]+)@([a-zA-Z0-9.-]+)\.([a-zA-Z]{2,4})') AS Emails FROM `mytable`;