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!

score 2 · Accepted Answer · edited May 23 '17 at 11:49

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 '17 at 11:49

Community

1
1

answered May 22 '12 at 23:58

web_bod

5,728
1
17
25

Not stellar, but I think I have an idea of the logic you are thinking of suggesting... :) – jonathanbell May 23 '12 at 00:32
Or plan B... how much data do you have? - could you rip it into CSV, process it as text and then upload? – web_bod May 23 '12 at 00:34
hmmm... interesting idea. I may just try that! Thanks! – jonathanbell May 23 '12 at 00:35
1

You could easily pull it into Textpad, etc. Gimme a shout if you need help with Regex – web_bod May 23 '12 at 00:38

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

1 Answers1