Skip to content
Advertisement

How to Update table row using regex

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 ” (“

Advertisement

Answer

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>
User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement