How to search and replace empty html table rows with line breaks in mysql database

Question

I need to search and replace this html in a mysql database ( via phpMyAdmin ) :

<tr> <td></td> <td></td> </tr>

But I don't know how to find it, as it has line breaks.

My current query is :

UPDATE `wp_posts` SET `post_content` = replace(post_content, '<tr> <td></td> <td></td> </tr>', '')

But obviously, it's not working.

How can I target it ?

Please provide sample data. It is unclear if you are looking to replace 4 different strings, or a string with 4 lines. — GMB
– GMB, Commented Feb 11, 2019 at 23:14
What version of MySQL are you using? If it's 8.0+ (or even MariaDB 10+), this will be easier — Phil
– Phil, Commented Feb 11, 2019 at 23:16
Also, it's "obviously not working" because you not only have newlines but 30 spaces before the start of each line in your from_str string — Phil
– Phil, Commented Feb 11, 2019 at 23:22

Derviş Kayımbaşıoğlu · Accepted Answer · 2019-02-11 23:23:47Z

you can use REGEXP_REPLACE. Please note that the provided regex both works for windows and linux style newlines. please also note that possible use of spaces are also considered in regex.

REGEXP_REPLACE(x, '<tr>(\s*\r*\n*\s*<td>\s*\r*\n*\s*</td>){2}\s*\r*\n*\s*</tr>', '<<replaced>>' )

\n is linux style newline
\r\n is windows style newline
\s is space character

create table t (x varchar(1000));

✓

insert into t values ('before <tr>\n<td></td>\n<td></td>\n</tr> after')

✓

select * from t

 | x | | :--------------------------------------------------- | | before <tr><br><td></td><br><td></td><br></tr> after |

select REGEXP_REPLACE(x, '<tr>(\s*\r*\n*\s*<td>\s*\r*\n*\s*</td>){2}\s*\r*\n*\s*</tr>', '<<replaced>>' ) from t

 | REGEXP_REPLACE(x, '<tr>(\s*\r*\n*\s*<td>\s*\r*\n*\s*</td>){2}\s*\r*\n*\s*</tr>', '<<replaced>>' ) | | :------------------------------------------------------------------------------------------------ | | before <<replaced>> after |

update t set x = REGEXP_REPLACE(x, '<tr>(\s*\r*\n*\s*<td>\s*\r*\n*\s*</td>){2}\s*\r*\n*\s*</tr>', '<<replaced>>' )

✓

db<>fiddle here

GMB · Accepted Answer · 2019-02-11 23:23:31Z

If you are looking to replace a string made of 4 lines, one thing you need to be careful about is line breaks. For example in your query you have additional spaces at the beginning of lines 2, 3 and 4, that probably will not match.

Depending on your set up, line breaks could be \r\n or \n.

UPDATE `wp_posts` SET `post_content` = REPLACE( post_content, '<tr>\n<td></td>\n<td></td>\n</tr>', '' )

If you are looking to replace 4 different strings, then you could either run the updates one by one, or generate a 4-level deedp nested REPLACE(). Here are examples for two string parts :

UPDATE `wp_posts` SET `post_content` = REPLACE(post_content, '<tr>', ''); UPDATE `wp_posts` SET `post_content` = REPLACE(post_content, '<td></td>', '');

Or :

UPDATE `wp_posts` SET `post_content` = REPLACE( REPLACE( post_content, '<td></td>', '' ), '<tr>', '' ) ;

Would someone please explain the anonymous downvote ? If anything is wrong with my answer I am willing to improve it.
Well the other solution seems better because it's watching for any space/line break between elements but the regex_replace functions is not working for me ... but your first snippet works like a charm, i needed to replace both \n and \r\n. Thank's !

Collectives™ on Stack Overflow

How to search and replace empty html table rows with line breaks in mysql database

2 Answers 2

Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Linked

Related