phpmyadmin Question posted in
The official documentation can be found here.

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

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 ?

Tags:

2

Answers


  1. 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 rn 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>', 
        ''
    )
;
    Login or Signup to reply.
  2. 0

    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
    • rn 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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search