How to Use Regexp in MySQL Replace Commands?

Question

Hi,

My goal is to replace the links in the database with a catchall link. I generally use the REPLACE command for replacing string in the database, but this time I am having difficulty because in order to find the links I need to use regular expressions, and that is simply not working out:

UPDATE node_revisions SET body = REPLACE ( `body` , 'http://.*.\pdf', '/migration-update' );

UPDATE node_revisions SET teaser = REPLACE ( `teaser` ,  'http://.*pdf', '/migration-update' );

These two queries just fall flat.

What needs to be done here in this situation?

Answer 1

As others have mentioned already, you can't do this in MySQL. However, this appears to be a one time operation you need to do so I wrote you a quick and dirty little php script to do the job. It assumes your node_revisions table has a primary key column called 'id'. If not, edit appropriately. Also, don't forget to change the database host, username, password and database name at the top of the script to match your configuration.

<?php
$host = '127.0.0.1';
$username = 'root';
$password = 'password';
$database = 'test';

$conn = mysql_connect($host, $username, $password);

if (!$conn) {
    echo "Unable to connect to DB: " . mysql_error();
    exit;
}

if (!mysql_select_db($database)) {
    echo "Unable to select " . $database . ": " . mysql_error();
    exit;
}

$sql = "SELECT * FROM node_revisions";

$result = mysql_query($sql);

if (!$result) {
    echo "Could not successfully run query ($sql) from DB: " . mysql_error();
    exit;
}

if (mysql_num_rows($result) == 0) {
    echo "No rows found, nothing to print so am exiting";
    exit;
}

while ($row = mysql_fetch_assoc($result)) {
    $id = $row['id'];
    $body = $row['body'];
    $teaser = $row['teaser'];
    $body = preg_replace('/http:\/\/.*?\.pdf/', '/migration-update', $body);
    $teaser = preg_replace('/http:\/\/.*?\.pdf/', '/migration-update', $teaser);
    $sql = "UPDATE node_revisions set body='" . mysql_real_escape_string($body) . "', teaser='" . mysql_real_escape_string($teaser) . "' where id=" . $id;
    mysql_query($sql);
}

mysql_free_result($result);
mysql_close($conn);
?>

Note also that I used a non-greedy modifier on the regular expressions so that if you have multiple pdf urls in a body or teaser field, you won't lose everything in between them.

Answer 2

REPLACE() does text replacement. There is no regular expression replacement in MySQL.

Answer 3

Short answer: you can't with base MySQL.

Long answer: this previous SO question, which mentions that you could do it with a UDF (user-defined function). However, this requires re-building MySQL to include the udf.