Question 63612 in PhpMyAdmin Question posted in
The official documentation can be found here.

MYSQL Remove string between two known strings – Phpmyadmin

I have a WordPress website and following a change of theme I have to modify the structure of a thousand links present on about fifty pages. I would like to do it in SQL via PHPMYADMIN.

Is there a way in SQL to remove the end of all my link with the following structure :

- <a href="https://website.com/cours/les-problemes/lecon/s1-2014-2015-mathematiques-les-problemes/">
- <a href="https://website.com/cours/la-division/lecon/s3-2014-2015-mathematiques-la-division-n-nakatani/">
- <a href="https://website.com/cours/mathematiques-larithmetique/lecon/201819-s5-fa-mathematiques-nathalie-nakatani/">

In order to only get :

- <a href="https://website.com/cours/les-problemes/">
- <a href="https://website.com/cours/la-division/">
- <a href="https://website.com/cours/mathematiques-larithmetique/">

I tried to use the answer of this topic : MYSQL Replace string between two known strings but I did not manage to find a solution to fit my purpose.

I also thought about doing it in two parts :
1- Remove the content between the ‘/lecon/‘ and the ‘">‘.
2- Then remove completely all the iteration of ‘/lecon/’ of my pages, because they only occur on the links that I want to edit.
But my knowledges in SQL are limited and I have no clue of how to do the first part.

My apologizes for my English.

Thanks in avance for any helps !

Tags:

2

Answers


  1. 0

    On MySQL 8+, we can try using a regex replacement:

    SELECT
    tag,
    REGEXP_REPLACE(tag, '(<a href="https?://(?:[^/]+/){3}).*">', '$1">')
FROM yourTable;

    Demo

    Login or Signup to reply.
  2. 0

    For MySQL < 8 (5.7), without REGEXP_REPLACE:

    SELECT REPLACE(CONCAT(TRIM(TRAILING SUBSTRING_INDEX(url, '/lecon/', -1) FROM url), '">'), 'lecon/', '') FROM `your_table`

    DEMO

    Using your idea, I removed all from /lecon/ to the end in STEP 1 and concatenated "> to repair the HTML URL, and then I replaced lecon/ with an empty string in STEP 2.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search