[SOLVED] UPDATE REPLACE random string with same beginning and ending in MariaDB 10

Aeolustw · 10-27-2023, 08:03 PM

Hi,
The strings:
<r>[quote name='Angel' date='Jan 5 2005, 21:36 PM']...
<r>[quote name='ABC' date='Mar 7 2005, 13:07 PM']...
<t>[quote name='DEF' date='Sep 2 2005, 22:10 PM']...

I want to replace [quote name='DEF' date='Sep 2 2005, 22:10 PM'] with <QUOTE><s>[quote]</s>
The beginning string is [quote name= and the end one is ']
And I try:

Code:

UPDATE phpbb_posts SET post_text = REGEXP_REPLACE (post_text, '\[quote name=.*\'\]' , '<QUOTE><s>[quote]<\/s>') WHERE post_text LIKE '%quote name=%' ;

The results data in show too many:
<r>[<QUOTE><s>[quote]</s><QUOTE><s>[quote]</s><QUOTE><s>[quote]</s>...

Ant hit?
Thanks.

NevemTeve · 10-28-2023, 12:35 AM

If you want to change lines with 'DEF', then it should be part of the WHERE-clause, e.g.

Code:

UPDATE phpbb_posts
SET post_text = REGEXP_REPLACE (post_text, from, to)
WHERE post_text LIKE '%quote name=%DEF%';

Aeolustw · 10-28-2023, 04:41 AM

NevemTeve,
Thanks,
I have 168 posts,
it fixed now.(no poster and post time)

Code:

UPDATE phpbb_posts SET post_text = REGEXP_REPLACE (post_text, "\[quote name=.*\']" , '<QUOTE><s>[quote]<\/s>') WHERE post_text LIKE '%quote name=%' ;

Aeolustw · 10-28-2023, 04:46 AM

If i want to keep poster and post time
change
<r>[quote name='Angel' date='Jan 5 2005, 21:36 PM']...
to
<r>[<QUOTE><s>[quote]</s>Angel' date='Jan 5 2005, 21:36 PM']...
Use these codes:

Code:

UPDATE phpbb_posts SET post_text = REPLACE (post_text, '[quote name=' , '<QUOTE><s>[quote]<\/s>') WHERE post_text LIKE '%quote name=%';