How to replace Wildcard url in sql?

Question

I want to replace all URLs that start with https://oneweburl.com/cm with https://anotherwebsite.com

i.e. URLs such as

https://oneweburl.com/cm/9304/434
https://oneweburl.com/cm/849/495/34
https://oneweburl.com/cm/2994/234/54

will be replaced with only https://anotherwebsite.com. So far I have tried

update wp_posts 
set post_content = 
    replace(post_content, 'https://oneweburl.com/cm/%', 'https://anotherwebsite.com');

Apparently that didn't work. Any idea the SQL to accomplish this? Thanks!

Tag your question with the database you are using.

Gordon Linoff
– Gordon Linoff

2018-02-05 21:56:30 +00:00
Commented Feb 5, 2018 at 21:56 — Gordon Linoff
– Gordon Linoff, Commented Feb 5, 2018 at 21:56
It is MySQL database

John
– John

2018-02-05 22:21:29 +00:00
Commented Feb 5, 2018 at 22:21 — John
– John, Commented Feb 5, 2018 at 22:21

Gordon Linoff · Accepted Answer · 2018-02-05 21:57:32Z

2

For the most part, you should be able to do:

update wp_posts
    set post_content = replace(post_content, 'https://oneweburl.com/cm/', 'https://anotherwebsite.com/')
    where post_content like 'https://oneweburl.com/cm/%';

This should work in any database.

The one issue is if post_content could have multiple URLs in the string. This will replace all matches to the specified string. If that is an issue, you can do string manipulations to do the right thing. Alas, those tend to depend on the specific database.

answered Feb 5, 2018 at 21:57

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

iSR5 Over a year ago

"http%oneweburl.com/cm/%" will handle the differences (https, http, www).

Collectives™ on Stack Overflow

How to replace Wildcard url in sql?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related