SQL query to remove text within parentheses?

Question

What is the best SQL query to remove any text within parenthesis in a mySQL database? I'd like something that works regardless of the position of the parenthesis in the text (beginning, middle, end, whatever). I don't care about keeping the text inside the parenthesis, just removing it.

Thanks!

Answer 1

It doesn't appear that MySQL has a regular expression replacement function, so the easiest way to modify existing data will likely be with an external application. Then keep all your new data clean before you add it to the database.

in PHP:

$result = $db->query();
while($row = $result->next){
  $value = preg_replace('/\(.*?\)/', '', $row->value);
  $db->query('UPDATE ... SET value = ' . $value . ' WHERE id = ' . $row->id);
}

note: I don't recall if PHP's Regex engine supports lazy repetition matching (.*?)

Answer 2

I figured it out:

UPDATE table SET column = CONCAT(SUBSTR(column,1,LOCATE('(', column)-1),SUBSTR(column,LOCATE(')', column)+1)) WHERE column LIKE '%(%' AND column LIKE '%)%'

This worked for my purposes, but it makes the following assumptions:

• parenthesis are properly matched
• only one pair of parenthesis exists in the string (no nesting, etc)