how to edit my links (sort and change by starting alphabet) in sql database [closed]

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

Answer

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

Leave a Reply

Your email address will not be published. Required fields are marked *