mysql - how to remove certain string from pattern?

Question

for example, I have a table row containing a string like this:

<p><img src="/assets/img/myfolder/abcdefghijkl0001_thumb.jpg"><br><br>some people can also type _thumb. if they want to <div style="background-image:url('/assets/img/myfolder/zzzzzz0002_thumb.png')"></div></p>

how do I remove just the "_thumb." substring inside the <img> and <div> part? I only know a little bit about replace command but I can't seem to make it work to only remove the "_thumb." substring inside those elements only..

Since MySQL 8.0 REGEXP_REPLACE can be used dev.mysql.com/doc/refman/8.0/en/… — Slava Rozhnev
– Slava Rozhnev, Commented May 19, 2020 at 10:15

houssam · Accepted Answer · 2020-05-19 11:49:45Z

1

NOTE: This is a helpful answer, but not the right one..!!
I think, REGEXP_REPLACE does not offer the possibility to replace groups.So, it is better to use some high-level language; PHP is good, to update your database.

<?php
$pat1=<<<EOF
/(<img\s+src\s*=\s*[\"\'].*)(_thumb)(\.(png|jpg)[\"\']>)/imU
EOF;
$pat2=<<<EOF
/(<div\s+style\s*=\s*[\'\"].*)(_thumb)(\.(png|jpg).*[\'\"]>)/imU
EOF;
$text=<<<EOF
<p><img src="/assets/img/myfolder/abcdefghijkl0001_thumb.jpg"><br><br>some people can also type _thumb. if they want to <div style="background-image:url('/assets/img/myfolder/zzzzzz0002_thumb.png')"></div></p>
EOF;
$text=preg_replace($pat1,'$1$3',$text);
$text=preg_replace($pat2,'$1$3',$text);
echo $text;
?>

The output:

<p><img src="/assets/img/myfolder/abcdefghijkl0001.jpg"><br><br>some people can also type _thumb. if they want to <div style="background-image:url('/assets/img/myfolder/zzzzzz0002.png')"></div></p>

Reqular Expression Online: https://regex101.com/r/sxY6OX/1

edited May 19, 2020 at 11:49

answered May 19, 2020 at 11:28

houssam

1,87316 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

dapidmini Over a year ago

what does the $1 and $3 mean in the second parameter? I've been reading the preg_replace docs for an hour now and I can't understand how it works to remove something from the pattern.. I only see how it would add something to it

houssam Over a year ago

The Parentheses determine the groups;regx=(here $1)(here $2) (here $3) ; so I have taken only the first and the last groups; so the text _thumb is the second group; will be neglected.read more about : ' regex groups captures ', stackoverflow.com/q/5642836/992406 ; also in the Reqular Expression Online (link in Answer), you can see the groups in the right-hand side.

Akina · Accepted Answer · 2020-05-19 10:46:11Z

0

SET @var := '<p><img src="/assets/img/myfolder/abcdefghijkl0001_thumb.jpg"><br><br>some people can also type _thumb. if they want to <div style="background-image:url(''/assets/img/myfolder/zzzzzz0002_thumb.png'')"></div></p>';

SET @exoticstr := CHAR(0);

SELECT 'original', @var
UNION ALL
SELECT 'replaced', REPLACE(REPLACE(REPLACE(@var, '_thumb.', @exoticstr), CONCAT(' ', @exoticstr, ' '), ' _thumb. '), @exoticstr, '.');

fiddle

You may use any char/substring as @exoticstr value - you must only guarantee that it is absent in column value. But I doubt that CHAR(0) may present in string-type column value.

edited May 19, 2020 at 10:46

answered May 19, 2020 at 10:27

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Collectives™ on Stack Overflow

mysql - how to remove certain string from pattern?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related