Question 22118 in Mysql Question posted in
The official MySQL documentation can be found here.

How to delete certain characters from table values? – Mysql

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?

Tags:

2

Answers


  1. 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.

    Login or Signup to reply.
  2. 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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search