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

0

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>', 
        ''
    )
;

answered Feb 11, 2019 at 23:23

GMB

224k25 gold badges103 silver badges151 bronze badges

2 Comments

GMB Over a year ago

Would someone please explain the anonymous downvote ? If anything is wrong with my answer I am willing to improve it.

Alexis Vandepitte Over a year ago

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

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related