CODEWITHSUNDEEP
mysql
David Bailey
David Bailey

Reputation: 623

Extract email address from mysql field

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?

Upvotes: 4

Views: 6364

Answers (5)

Saj
Saj

Reputation: 875

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

Upvotes: 5

RMathis
RMathis

Reputation: 590

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

Upvotes: 5

Yousf
Yousf

Reputation: 3997

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

Upvotes: 3

hd1
hd1

Reputation: 34677

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.

Upvotes: 1

Ross Smith II
Ross Smith II

Reputation: 12189

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.

Upvotes: 0

Related Questions