MYSQL Remove string between two known strings-CodePudding

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 !

CodePudding user response：

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

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

Demo

CodePudding user response：

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.