0

Currently, I'm trying filter my database removing all links and leaving only the file name.

Here is an example:

table1

id   dub

1    http://svr1.mystream.com/data/files/media/p1/file1.mp4 

2    http://svr1.mystream.com/data/files/media/p0/file1.mp4

3    http://svr1.mystream.com/data/files/media/file48.mp4

...

Also, I've another table with the same content, but they are separated by a comma.

table2

id   leg

1    http://svr1.mystream.com/data/files/media/p1/file1.mp4, http://svr1.mystream.com/data/files/media/p1/file2.mp4, http://svr1.mystream.com/data/files/media/p1/file3.mp4 

2    http://svr1.mystream.com/data/files/media/file48.mp4, http://svr1.mystream.com/data/files/media/p1/file49.mp4, http://svr1.mystream.com/data/files/media/p1/file50.mp4

3    http://svr1.mystream.com/data/files/media/p0/file13.mp4, http://svr1.mystream.com/data/files/media/p1/file14.mp4, http://svr1.mystream.com/data/files/media/p1/file15.mp4

...

The output from the table2 should be something file1.mp4, file2.mp4, file3.mp4, ...

I must remove everything and leave only the file name after the last "/" character.

What should I do to solve this problem?

inukix
  • 79
  • 7

1 Answers1

0

Mysql 8 has regexp replace:

https://dev.mysql.com/doc/refman/8.0/en/regexp.html#function_regexp-replace

For lower version mysql use substringindex of last '/' and substring (for first table)

https://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_substring-index

Second table must be normalized and looks like first table. Without rexexp replace I don't know how to do it.

Crutch Master
  • 189
  • 5
  • Also see https://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql – kmoser Dec 27 '18 at 04:25