phpmyadmin Question posted in
The official documentation can be found here.

SQL statement to replace final part of an URL – Phpmyadmin

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.

Tags:

2

Answers


  1. 0

    You can do:

    update t
    set url = concat(substring_index(url, '=', 1), '.html')
    where url like '%-%'
    Login or Signup to reply.
  2. 0

    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%'
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search