I have a table. In one of the columns of the table , the values have this form:
Value1(12) Value2(45) Value3(35) Value4(37) Value5(17)
How to delete the opening parenthesis, the value inside the parentheses and the closing parenthesis? So that after updating the values would take this form:
Value1 Value2 Value3 Value4 Value5
P.s: It seems that regular expressions will help here, but how to form a query with them?
CodePudding user response：
Use regexp_replace in an update.
update some_table
set some_column = regexp_replace(some_column, '\\([^)]*\\)', '')
That says to replace anything between () that isn't ), including the ().
Note that ( and ) have special meaning in a regex so they must be escaped with a \. \\ is required because \ is also the string escape character. '\(' becomes '(' and the escaping is lost. '\\(' becomes '\(' and the escape is passed along to the regex.
CodePudding user response：
The simplest way is to use REGEXP_REPLACE which is supported on all mysql 8.0 and above versions.
UPDATE mDemo SET value = REGEXP_REPLACE(value, '[(][0-9]{2,}[)]', '')
Explaination:
[(][0-9]{2,}[)]
This basically looks for a (two digits or above) and replaces it with an empty string, thus giving you the desired result.