0

i have a database (mysql) with links like:

mysite.com/Movies/Abajan_1080p.mp4  
mysite.com/Movies/Abajan_720p.mp4  
mysite.com/Movies/Abajan_480p.mp4  
mysite.com/Movies/Bahman_1080p.mp4  
mysite.com/Movies/Bahman_720p.mp4  
mysite.com/Movies/Bahman_480p.mp4

.... i sorted my files by creating directories by alphabets (A,B,...) so i need to change links:

mysite.com/Movies/A/Abajan_1080p.mp4  
mysite.com/Movies/A/Abajan_720p.mp4  
mysite.com/Movies/A/Abajan_480p.mp4  
mysite.com/Movies/B/Bahman_1080p.mp4  
mysite.com/Movies/B/Bahman_720p.mp4  
mysite.com/Movies/B/Bahman_480p.mp4

is there a simple solution to edit database? to edit all links like that? Thanks a lot

Shadow
  • 33,525
  • 10
  • 51
  • 64
Advil
  • 11
  • 2
  • All the values start with `mysite.com/Movies/` and are followed by the file name or there other folders and subfolders? – forpas Mar 07 '21 at 07:53
  • yes all movies links start with mysite.com/Movies/ , and then file name like this: Movies/Abajan_1080p.mp4 – Advil Mar 07 '21 at 08:00
  • Since SQL includes data definition, a [mcve] for an [SQL question](//meta.stackoverflow.com/q/333952/90527) should include [DDL](//en.wikipedia.org/wiki/Data_definition_language) statements for sample tables and [DML](//en.wikipedia.org/wiki/Data_manipulation_language) statements for sample data (rather than a dump or ad hoc format). Desired results don't need to be presented as sample code, as results are the output of code and not code themselves. – outis Mar 24 '22 at 21:40
  • Duplicate of [Update a column value, replacing part of a string](https://stackoverflow.com/q/10177208/90527). – outis Mar 24 '22 at 21:42

1 Answers1

1

You can use the function SUBSTRING_INDEX() to get the filename and then concatenate the path:

UPDATE tablename
SET col = CONCAT(
  'mysite.com/Movies/',
  LEFT(SUBSTRING_INDEX(col, '/', -1), 1),
  '/',
  SUBSTRING_INDEX(col, '/', -1)
)

Replace col with your column's name.

See the demo.
Results:

col
mysite.com/Movies/A/Abajan_1080p.mp4
mysite.com/Movies/A/Abajan_720p.mp4
mysite.com/Movies/A/Abajan_480p.mp4
mysite.com/Movies/B/Bahman_1080p.mp4
mysite.com/Movies/B/Bahman_720p.mp4
mysite.com/Movies/B/Bahman_480p.mp4
forpas
  • 160,666
  • 10
  • 38
  • 76