[FIXED] SQL statement to remove part of an URL on column

Issue

I have a videos table with a column URL with many different URL types

https://google.com/questions/ask?963
https://google.com/embed/ask
https://google.com/top/123.html
https://video.net/embed-ask?963
https://video.net/embed-123.html
https://video.net/top?123.html

I need to delete part of a specific URL (delete embed-) from

https://video.net/embed-75mdabvgl3do.html

to

https://video.net/75mdabvgl3do.html

I have tray this SQL but return an empty result (0 rows affected)

UPDATE `videos` SET url = REPLACE(url, '%video.net/embed-%', '%video.net/%') WHERE `url` LIKE '%video.net/embed-%';

Solution

You could try this:

UPDATE videos
SET url = REPLACE(url, 'video.net/embed-', 'video.net/')
WHERE url LIKE '%video.net/embed-%';

This hopefully would be specific enough of a replacement. If not, we could consider using regular expressions (available if using MySQL 8+).

Answered By - Tim Biegeleisen