CODEWITHSUNDEEP
mysqlphpmyadmin
Sara44
Sara44

Reputation: 422

Replacing some URLs with another

I have a MySQL 5.7.29 database on which a website is built. I need to construct a query to find all table rows containing lines such as

https://example.com/index.php?topic=7989.0

or similar and replace them with

https://example.com/my-redirect-page/?7989.0

The wildcard here is the ?topic=7989.0 as this can be something like ?topic=1234 or even ?topic=3456.0#anchor

I can display the rows they appear in (in PHPMyAdmin) using this (thanks to 'sticky bit' below) :

SELECT * FROM `abc_posts` WHERE `post_content` LIKE '%example.com\index.php?topic%'

My problem is that I then need to change just that URL when there is also text content around it.

Thanks in advance.

Upvotes: 0

Views: 45

Answers (2)

nbk
nbk

Reputation: 49375

You could do something like thin to find them

SELECT 'https://example.com/index.php?topic=7989.0'
WHERE 'https://example.com/index.php?topic=7989.0' REGEXP 'example.com/index.php?topic=';

Which would find all rows. But for replacing it, you must also tell which database version youh have mysql 5.x have not many regex fucntions mariadb and mysql 8 have much more

Upvotes: 1

sticky bit
sticky bit

Reputation: 37472

The question mark doesn't need to be escaped.

But 'https://example.com/index.php?topic=7989.0' isn't LIKE '%example.com\?topic%' as the question mark doesn't immediately follow the host name.

Try:

...
post_content LIKE '%example.com/index.php?topic%'
...

Upvotes: 1

Related Questions