How to Update table row using regex

Question

I have a table with rows like this:

|column A                              |

|normal data 1 (some data to remove 1) |
|normal data 2 (some  data to remove 2)|
|normal data 3 (some  data to remove 3)|
|normal data 4 (some  data to remove 4)|
|........                              |

and I want to update my rows to keep only normal data and delete data inside "()" I'm wondering if there is a way to use regex inside my SQL update statement to delete all data after " ("

http://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql — Ben, Mar 21 '17 at 20:50

score 1 · Answer 1 · answered Mar 21 '17 at 20:52

1

I think the easiest way is substring_index():

update t
    set col = substring_index(col, ' (', 1)
    where col like '% (%';

answered Mar 21 '17 at 20:52

Gordon Linoff

1,198,228
53
572
709

Doesn't this assume there is no '(' characters before the intended one? – Ben Mar 21 '17 at 20:57

score 1 · Accepted Answer · answered Mar 21 '17 at 20:56

You must use 2 SUBSTRING_INDEX like this if there also some data behind the ).

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX( YourFieldName , '(', 1),')',-1);

sample

mysql> SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('normal data 1 (some data to remove 1)', '(', 1),')',-1);
+------------------------------------------------------------------------------------------+
| SUBSTRING_INDEX(SUBSTRING_INDEX('normal data 1 (some data to remove 1)', '(', 1),')',-1) |
+------------------------------------------------------------------------------------------+
| normal data 1                                                                            |
+------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

mysql>

score 1 · Answer 3 · answered Mar 21 '17 at 21:16

Try to use replace, substring and position functions to eliminate the data between ().

update tablename set col=replace(col,substr(col,position('(' in col),position(')' in col)-position('(' in col)),' ');

then it will update all the data between () inclunding braces.

How to Update table row using regex

3 Answers3