Regular expression to replace broken email links

Question

Problem: authors have added email addresses wrongly in a CMS - missing out the 'mailto:' text.

I need a regular expression, if possible, to do a search and replace on the stored MySQL content table.

Cases I need to cope with are:

1. No 'mailto:'
2. 'mailto:' is already included (correct)
3. web address not email - no replace
4. multiple mailto: required (more than one in string)

Sample string would be: (line breaks added for readability)

<a href="add1@test.com">add1@test.com</a> and
<a href="mailto:add2@test.com">add2@test.com</a> and
<a href="http://www.test.com/">real web link</a>
second one to replace <a href="add3@test.com">add3@test.com</a>

Required output would be:

<a href="mailto:add1@test.com">add1@test.com</a> and
<a href="mailto:add2@test.com">add2@test.com</a> and
<a href="http://www.test.com/">real web link</a>
second one to replace <a href="mailto:add3@test.com">add3@test.com</a>

What I tried (in PHP) and issues:

pattern:   /href="(.+?)(@)(.+?)(<\/a> )/iU
replacement:    href="mailto:$1$2$3$4

This is adding mailto: to the correctly formatted mailto: and acting greedily over the last two links.

Thanks for any help. I have looked about, but am running out of time on this as it was an unexpected content issue.

If you are able to save me time and give the SQL expression, that would be even better.

Answer 1

You need to apply a proper mail pattern first (e.g: Using a regular expression to validate an email address), second search for mailto:before mail or nothing (e.g: (mailto:|)), and last preg_replace_callback suits for this.

This looks like working as you wish (searching only email addresses in double quotes);

$s = '<a href="add1@test.com">add1@test.com</a> and 
<a href="mailto:add2@test.com">add2@test.com</a> and 
<a href="http://www.test.com/">real web link</a> 
second one to replace <a href="add3@test.com">add3@test.com</a>';
echo preg_replace_callback(
    '~"(mailto:|)([_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,4}))"~i', 
    function($m) {
        // print_r($m); @debug
        return '"mailto:'. $m[2] .'"';
    },
    $s
);

Output as you desired;

<a href="mailto:add1@test.com">add1@test.com</a> and 
<a href="mailto:add2@test.com">add2@test.com</a> and 
<a href="http://www.test.com/">real web link</a> 
second one to replace <a href="mailto:add3@test.com">add3@test.com</a>

Answer 2

Try replace

/href="(?!(mailto:|http:\/\/|www\.))/iU

with

href="mailto:

?! loosely means "the next characters aren't these".

Alternative:

Replace

/(href=")(?!mailto:)([^"]+@)/iU

with

$1mailto:$2

[^"]+ means 1 or more characters that aren't ".

You'd probably need a more complex matching pattern for guaranteed correctness.

MySQL REGEX matching:

See this or this.

Answer 3

Use the following as pattern:

/(href=")(?!mailto:)(.+?@.+?")/iU

and replace it with

$1mailto:$2

(?!mailto:) is a negative lookahead checking whether a mailto: follows. If there is no such one, remaining part is checked for matching. (.+?@.+?") matches one or more characters followed by a @ followed by one or more characters followed by a ". Both + are non-greedy.

The matched pattern is replaced with first capture group (href=") followed by mailto: followed by second capture group (upto closing ").