MySQL remove characters between two brackets

Question

I am going through some data in MySQL and I need to remove some information from a column which looks likes this.

**sometext** (something else)

The data is inconsistent unfortunately; not all of the data follows this structure, some of it just has the first part.

**sometext**

So I need a way to remove everything between the brackets and then the brackets themselves in order to just leave the **sometext** part.

Answer 1

Use this solution:

UPDATE tbl
SET    txt = REPLACE(txt, SUBSTRING(txt, LOCATE('(', txt), LENGTH(txt) - LOCATE(')', REVERSE(txt)) - LOCATE('(', txt) + 2), '')
WHERE  txt LIKE '%(%)%'

Keep in mind that due to the limitation of MySQL's minimal regex support, this will not work for (will return blank):

(replacetext1) keepthistext1 (replacetext2) keepthistext2 (replacetext3)

...if you want to keep keepthistext1 and keepthistext2 since it will remove everything between the first opening parenthesis and the last closing parenthesis.

SQLFiddle Demo

Answer 2

you could try using function SUBSTRING_INDEX. The syntax is:

SUBSTRING_INDEX(str,delim,count)

In this case it could be something like:

UPDATE tbl
SET    txt = (SELECT SUBSTRING_INDEX(txt, '(', 1));

This would help, though i have not checked the query. For further reference on this function you can look here

Answer 3

MySQL doesn't support regular expressions, so the only way will be to use an external program written in PHP etc, or use stored procedures.