Are you looking for something like this:
UPDATE table
SET field =
REPLACE("http://one.two.com/three/xxx/yyy/zzz/", "http://domain.com/images/");
This should work with MySQL but I am not sure if this works with other DB systems.
Oh sorry, it was:
UPDATE table
SET field =
REPLACE(
field,
"http://one.two.com/three/xxx/yyy/zzz/",
"http://domain.com/images/");
I’m having problems …
UPDATE wp_posts
SET post_content=(
REPLACE(
post_content,
“http://the.old.domain/img/%/%/%/”,
“http://www.the-new-domain.com/images/”))
as the /xxx/yyy/zzz in the example vary, so I used % as a wildcard. But … when I run that, it says 0 rows changed, yet what I have between the “” is correct.
What am I missing ?
I expect this not to work, because the % wildcard will only work in WHERE statements. But you may modify the statement by using the MySQL regular expression support http://dev.mysql.com/doc/refman/4.1/en/regexp.html
UPDATE wp_posts
SET post_content = "http://www.the-new-domain.com/images/"
WHERE post_content REGEXP ".*http://the.old.domain/img/alpha*/alpha*/alpha*/.*"
I haven’t tested this nor am I sure the regular expression does the job for you. You may have to extend the expected characters between the path separators as well.
Okay, this will also not solve your problem and it seems that there is currently no pure SQL way to do this: http://forums.mysql.com/read.php?20,54128,54199
Thanks for trying – it’s appreciated 🙂
Ahh……. export .sql, do regex replace, import .sql ?
This may work. In this case you’d only need a texteditor with regex capabilities.
