How to update and replace string with random number using a regular expression in MySQL?

Question

I'm trying to update a Wordpress table (wp_posts) that is using a URL with a two digit random number between 10 and 20 inside the URL to a new URL for a move to a new CDN. Here is an example of what I am trying to do:

UPDATE wp_posts SET post_content = REPLACE (post_content, 'http://media13.oldcdn.com', 'http://newcdn.com');

That's fine and would (I believe) work well for what I want to accomplish except the number 13 in the example above could be any number between 10 and 20.

I think that I want something like this (but I am not savy in the ways of the database):

UPDATE wp_posts SET post_content = REPLACE (post_content, 'http://media REGEXP '\d\d' .oldcdn.com', 'http://newcdn.com');

Please help!

Community · Accepted Answer · 2017-05-23 11:49:09Z

2

SQL supports wild cards in query strings in where clauses - e.g.

LIKE 'http://media__.oldcdn.com'

but not in Replace - you'll probably need a script for what you're trying to do. I found a solution here: How to Use Regexp in MySQL Replace Commands?

edited May 23, 2017 at 11:49

CommunityBot

11 silver badge

answered May 22, 2012 at 23:58

web_bod

5,7681 gold badge19 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

jonathanbell Over a year ago

Not stellar, but I think I have an idea of the logic you are thinking of suggesting... :)

web_bod Over a year ago

Or plan B... how much data do you have? - could you rip it into CSV, process it as text and then upload?

jonathanbell Over a year ago

hmmm... interesting idea. I may just try that! Thanks!

web_bod Over a year ago

You could easily pull it into Textpad, etc. Gimme a shout if you need help with Regex

Collectives™ on Stack Overflow

How to update and replace string with random number using a regular expression in MySQL?

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related