0

I'm trying to search and replace for a link with wildcard in phpMyadmin.

If I simulate this query

UPDATE wp_posts SET post_content = REPLACE (post_content, '<a style="text-decoration:none" href="/spammy-url">.</a>','.');

It finds the exact match and will replace them successfully.

However due to the amount of links with different URL's i'd like to wildcard the query, If I navigate to the wp_posts table and do a search and replace, it displays all the correct entries that need to be replaced, however the column showing the "replaced" string doesnt change.

I thought doing the following would do it :

UPDATE wp_posts SET post_content = REPLACE (post_content, '<a style="text-decoration:none" href="%">.</a>','.');

But it's not returning any results. Do wildcards not work with search and replace in this way ?

If someone could explain where I am going wrong that would be great.

UPDATE

I have just tried to run the query via SSH and this is the notice I get :

Query OK, 0 rows affected (0.02 sec)
Rows matched: 3196  Changed: 0  Warnings: 0

So it found the rows required to change, but didnt change them. I think its something to do with the wildcard ( obviously ) but everything im trying comes up with the same result.

  • No as they don't do wildcard search and replace, hence having to revert to Mysql – Randomer11 Mar 7 at 8:11
  • MySQL does not support the syntax you're attempting. If you want to do this type of replace you'll need to use a regular expression with REGEXP_REPLACE. Note that this only works in MySQL 8+, which is quite new. Any further questions on pure MySQL should be asked at stackoverflow.com. They're off-topic here. – Jacob Peattie Mar 7 at 9:33
  • Ah that makes sense, I use MariaDB 10+ which I believe also supports it : mariadb.com/kb/en/library/regexp_replace Just to figure out what the ragex would be now :S Any help appreciated . – Randomer11 Mar 7 at 9:43
  • You're more likely to get help for generic SQL questions on stackoverflow.com. – Jacob Peattie Mar 7 at 9:48
  • Thanks , Yes I think im on the right track now. Found a few related questions regarding REGXP REPLACE, just needed to be pointed in the right direction :) Thanks Jacob – Randomer11 Mar 7 at 9:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.