Replace URL Path Keeping Only Last Part - DB Query

Question

We have multiple URL's in the database like:

http://www.example.com/_73737337/dir1/dir2/image.jpg
http://www.example.com/_892/direc/picture.png
http://www.example.com/_029929292929/dir/did/dic/dif/gallery.gif

All different length paths, different directories etc. What I need is to keep the file name at the end and replace everything before it with a new domain and directory.

For example:

http://www.example.com/_73737337/dir1/dir2/image.jpg

Will become:

http://www.newexample.com/images/image.jpg

AND

http://www.example.com/_029929292929/dir/did/dic/dif/gallery.gif

Will become:

http://www.newexample.com/images/gallery.gif

Is there any way to do this via a query to the database? There are around 3000 URLs to update.

These URLs sit inside content and not standalone entries in the database. So the query has to pick them out of the content field and ignore the rest of the content.

You can do it with .htaccess , sql and php. Just replace the path and update same links — DopeAt
– DopeAt, Commented Feb 27, 2018 at 17:15
I don't know all the paths as they're embedded in post content and there are around 3000 of them. I don't want to add line by line in the htaccess. I am not sure how I can write a sql query which is my purpose for asking to achieve what I have asked. — andy
– andy, Commented Feb 27, 2018 at 17:17
If you don't know all paths and they not same I am afraid you have to go one by one. I am not sure if there is a way to do it at once with just one script, I hope there is and someone help you, but if not try to combine group by the links and see how many different there are there — DopeAt
– DopeAt, Commented Feb 27, 2018 at 17:19
Try what David gave as answer seems like its what you asked for — DopeAt
– DopeAt, Commented Feb 27, 2018 at 17:23
It would be a nightmare without regexp_replace. If your version doesn't support that, you should write a script in PHP and update the content in a loop. — Paul Spiegel
– Paul Spiegel, Commented Feb 27, 2018 at 17:50

David Breen · Accepted Answer · 2018-02-27 17:20:11Z

2

You could use SUBSTRING_INDEX() with '/' as the delimiter.

Example

SELECT SUBSTRING_INDEX(image_url, '/', -1) AS filename
FROM table_name

Here is a link to the docs for the function: https://www.w3resource.com/mysql/string-functions/mysql-substring_index-function.php

answered Feb 27, 2018 at 17:20

David Breen

2471 silver badge8 bronze badges

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

2 Comments

andy Over a year ago

Like the answer but unfortunately I didn't specify that these URLs are within content not in their own column in the database. So there may be 1000 words and 3 urls within those words. I've updated the original post.

David Breen Over a year ago

Sorry I'm not sure how you would accomplish that. Probably using regular expressions like Paul Spiegel mentioned above

Collectives™ on Stack Overflow

Replace URL Path Keeping Only Last Part - DB Query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related