0

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?

Salvino D'sa
  • 4,018
  • 1
  • 7
  • 19
Serj
  • 27
  • 4

2 Answers2

0

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.

Demonstration.

Schwern
  • 153,029
  • 25
  • 195
  • 336
  • Is it possible to do something like this code in MySql <8? – Serj Nov 23 '22 at 06:23
  • @artjob [`regexp_replace` is not in 5.7](https://dev.mysql.com/doc/refman/5.7/en/string-functions.html). You'll have to [do it with string functions](https://stackoverflow.com/questions/59319704/looking-for-regexp-replace-alternative-on-mysql-5-7-27). There's a lot you can't do in MySQL < 8. – Schwern Nov 23 '22 at 18:34
0

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.

Demo

Salvino D'sa
  • 4,018
  • 1
  • 7
  • 19