CODEWITHSUNDEEP
phpmysqlregexreplace
Cy.
Cy.

Reputation: 2145

Find and replace braced tags within a MySQL table

I have about 40000 records in that table that contains plain text and within the plain text, contains that kind of tags which its only characteristic is that they are braced between [ ] 

[caption id="attachment_2948" align="alignnone" width="480" caption="the caption goes here"]

How could I remove those? (replace by nothing)

I could also run a PHP program if necessary to do the cleanup.

Upvotes: 2

Views: 301

Answers (4)

YOU
YOU

Reputation: 123791

Try

$text = preg_replace('/\[\w+(?:\s+\w+="[^"]+")+\s*\]/', '', $text)

Note:

  • there should be at least one attribute inside the tag (eg., [caption id="attachment_2948"], just [caption] will not match)
  • attributes must be inside double quotes ("attachment_2948")
  • there is no \" inside the attributes quotes (this will not work - "attachme\"nt_2948")
  • you can have [] inside the attributes (eg., [caption caption="the [caption] goes here"])
  • and make sure you did database backup before changing anything.

Upvotes: 2

Your Common Sense
Your Common Sense

Reputation: 157838

you will need to run a PHP program, as mysql have no regexp based replace.
'~\[.*?\]~' pattern would be enough

Upvotes: 0

Michal Čihař
Michal Čihař

Reputation: 10091

There is no easy way to do this in MySQL - it does not have regexp based replaces. The easiest way is to load all rows and do replacement in PHP, something like:

$result = mysql_query('SELECT * FROM table');
while ($row = mysql_fetch_array($result)) {
   $id = $row['ID'];
   $field = $row['Field'];
   $field = preg_replace('/\[[^\]]+\]/', '', $field); 
   $escaped = mysql_real_escape_string($field);
   $sql = mysql_query('UPDATE table SET Field = ' . $escaped . ' WHERE ID = ' . $id);
}

Upvotes: 1

Petr Peller
Petr Peller

Reputation: 8826

It's that easy or I missed something?

$text = preg_replace('/\[[^[]*\]/', $text);

Upvotes: 0

Related Questions