Find and replace braced tags within a MySQL table

Question

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.

Answer 1

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.

Answer 2

It's that easy or I missed something?

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

Answer 3

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);
} 

Answer 4

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