CODEWITHSUNDEEP
phpmysql
andy
andy

Reputation: 459

Replace URL Path Keeping Only Last Part - DB Query

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.

Upvotes: 0

Views: 353

Answers (1)

David Breen
David Breen

Reputation: 247

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

Upvotes: 2

Related Questions