Toxic Byte

I’ve used regex in postgres a lot, but I’ve got a mysql database with a lot of fields that need updating. They’re in the form of “http://url/?page=keyword” and I want to change that to “http://url/page/keyword/”. So I experiment, but yield no results, in fact errors:

SELECT *
FROM `domain`
WHERE redir REGEX `^http`

Which is annoying. Obviously, I can run a query to select the things with a LIKE operator, but I want to retain a portion of the field for the UPDATE. I might have to resort to using substring commands and the || operator. But I want to do it with a regular expression first.

It turns out that it’s actually called REGEXP and not REGEX when I actually read the documentation. And the expression itself shouldn’t be in backticks either! I’m not sure it’s possible to do an update with a regex to pull out what you need, but I’m actually over complicating the issue, and it solved using the REPLACE command!

UPDATE domain
SET redir = REPLACE(
redir,
"http://url/?page=",
"http://url/page/")
WHERE redir LIKE "http://url/?page=%"
AND domain_id = 1;

Now, I actually wanted a trailing slash at the end too, which I could have done in one foul swoop with a regexp, but I’ll do a simple concatenation to sort that out. Bizarrely, this manages to set things to zero (both with the & and the || operators):

UPDATE domain
SET redir = redir & "/"
WHERE redir LIKE "http://url/page/%"
AND redir NOT LIKE "%/"
AND domain_id = 1;

(The domain_id is in there for damage limitation, thankfully and the NOT LIKE part is so we don’t double up and that already end in a slash).

And we end up with:

UPDATE domain
SET redir = CONCAT(redir, "/")
WHERE redir LIKE "http://url/page/%"
AND redir NOT LIKE "%/";

Ithankyew.

Technorati Tags: mysql, sql, regex

Post a Comment | Trackback URI

New! We've just started a message board and discussion forum over on the Toxic Byte Technical Forum. It's a bit lonely over there at the moment, but feel free to join in and participate!