CODEWITHSUNDEEP
mysqlmysql-regexp
Serj
Serj

Reputation: 27

How to delete certain characters from table values?

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?

Upvotes: 0

Views: 33

Answers (2)

Salvino D'sa
Salvino D'sa

Reputation: 4506

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

Upvotes: 0

Schwern
Schwern

Reputation: 165396

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.

Upvotes: 0

Related Questions