[FIXED] SQL statement to replace final part of an URL

Issue

Replace in a videos table with a column URL with many different URL types. I use MariaDB 10.3

https://google.com/questions/ask?963
https://google.com/embed/57=66.88.028.10/i/03/077fsdf
https://google.com/top57=66.88.028.10/i/03/077
https://video.net/emb.html?asdeen45dr57=66.88.028.10/i/03/07776/asdeen45dr57
https://video.net/fomdfk5f7s1f.html
https://video.net/emb.html?qsfeen4gttv1=54.47.158.810/i/11/00036/qsfeen4gttv1

I need to delete part of a specific URL (delete all after =) and replace with .html

from

https://video.net/emb.html?asdeen45dr57=66.88.028.10/i/03/07776/asdeen45dr57

to

https://video.net/emb.html?asdeen45dr57.html

In this case, will replace

=66.88.028.10/i/03/07776/asdeen45dr57

with

.html

Please note, the URL part, after the = sign, are different for each URL.

Solution

If you are running MySql 8.0 or later you can use regexp_replace

UPDATE videos
SET url = REGEXP_REPLACE(url, '=.*$', '.html')
WHERE url LIKE 'https://video.net%'

Without regular expression this will work (also fine for MySql 5.*)

UPDATE videos 
SET url = CONCAT(SUBSTR(url, 1, INSTR(url, '=') - 1), '.html')
WHERE url LIKE 'https://video.net%'

Answered By - Joakim Danielson