mySQL: Removing a regex substring

Question

I have urls stored in my database table in the following format

http://domain.com/images/4/8/48bafb746bb7baa695481574afc345eb61af8d0a.jpg, and http://domain.com/images/0/f/0f602869e208139d2da67867359fb7cf092eb02b.jpg.jpg

I want to change the directory the file are stored in and reflect this change in the DB. Something like the following.

http://domain.com/images/48bafb746bb7baa695481574afc345eb61af8d0a.jpg, and http://domain.com/images/0f602869e208139d2da67867359fb7cf092eb02b.jpg.jpg

I simply want to move all images up two directories. How can I remove the top two directories from the string?

mysql regexes can only match. they cannot replace or alter. You'd need to use standard mysql string operations for the changes, or do the search/replace in client-side code. — Marc B
– Marc B, Commented Jun 2, 2014 at 16:41
Are the directory names always exactly one character as you have above? (that makes string ops a little easier) — Michael Berkowski
– Michael Berkowski, Commented Jun 2, 2014 at 16:43
You don't need regexp for this, it looks like you should be able to do it with substring_index. — Barmar
– Barmar, Commented Jun 2, 2014 at 16:45
(?:[^\/]+\/){2}([^\/]+)$, be careful if there are not more than 2 directories and you use this. — Sam
– Sam, Commented Jun 2, 2014 at 16:51

neiha · Accepted Answer · 2014-06-02 18:04:14Z

3

try with substring_index

 select concat('http://domain.com/images/', substring_index('http://domain.com/images/4/8/48bafb746bb7baa695481574afc345eb61af8d0a.jpg','/',-1))

to edit your records you could do something like this:

 update yourtable set yourcoulmnpath= concat('http://domain.com/images/', substring_index(yourcoulmnpath,'/',-1))

edited Jun 2, 2014 at 18:04

answered Jun 2, 2014 at 17:39

neiha

1715 bronze badges

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

3 Comments

andre Over a year ago

That works like i want it too. I'll try to use this to change every entry. Any chance you know how to write a select statement or function to take advantage of this?

neiha Over a year ago

I edited my answer to show you how you could update your records

zx81 Over a year ago

Nice and efficient neiha :) +1

Collectives™ on Stack Overflow

mySQL: Removing a regex substring

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related