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?

Of course yes. Please explain more what you have try so far and what is your database structure. — lvarayut, Jun 12 '13 at 16:32
Basically, I have a "description" column and another column called "email" and I need to look for the email address in the description and add to the email column. I think I will write a quick Python script to do this, but was interested if there was a way to do it directly in the database. — David Bailey, Jun 12 '13 at 17:16

score 5 · Answer 1 · answered Jul 16 '20 at 01:25

5

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`;

answered Jul 16 '20 at 01:25

Saj

781
8
7

score 4 · Answer 2 · answered Mar 09 '17 at 23:08

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))

score 3 · Answer 3 · edited May 23 '17 at 12:25

3

You can't select matched part only from Regular expression matching using pure Mysql. You can use mysql extension (as stated in Return matching pattern, or use a scripting language (ex. PHP).

edited May 23 '17 at 12:25

Community

1
1

answered Jun 12 '13 at 16:35

Yousf

3,957
3
27
37

@hd1 I was talking about matching (selecting the matched part only). You can't do this using pure mysql. – Yousf Jun 13 '13 at 09:29

score 1 · Answer 4 · answered Jun 12 '13 at 18:34

1

MySQL does have regular expressions, but regular expressions are not the best way to match email addresses. I'd strongly recommend using your client language.

answered Jun 12 '13 at 18:34

hd1

33,938
5
80
91

of course you can match a regex, but you can't select the matched part only. – Yousf Jun 13 '13 at 09:26
Which is why I *strongly recommended using your client language* – hd1 Jun 13 '13 at 15:55

score 0 · Answer 5 · answered Jun 12 '13 at 18:27

If you can install the lib_mysqludf_preg MySQL UDF, then you could do:

SET @regex = "/([a-z0-9!#\$%&'\*\+\/=\?\^_`\{\|\}~\-]+(?:\.[a-z0-9!#\$%&'\*\+\/=\?^_`{\|}~\-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+(?:[A-Z]{2}|aero|arpa|asia|biz|cat|com|coop|edu|gov|info|int|jobs|mil|mobi|museum|name|net|org|post|pro|tel|travel|xxx))/i";

SELECT 
    PREG_CAPTURE(@regex, description) 
FROM 
    example 
WHERE
    PREG_CAPTURE(@regex, description) > '';

to extract the first email address from the description field.

I can't think of another solution, as the REGEXP operator simply returns 1 or 0, and not the location of where the regular expression matched.

Extract email address from mysql field

5 Answers5