CODEWITHSUNDEEP
mysqlsqlregexstringreplace
Sherry
Sherry

Reputation: 21

MySQL Question - I want to eliminate all text within any parenthesis

Just checking to see if any of you would have a solution for this – from the below text like this I want to eliminate all text within any parenthesis.

Input –

PAY - addition,FILES (aaaaaaaaaaaaaa/bbbbbbbbbbbs i.e. ssss,ffff – i.e. cccccc),DED (ppppppp, llllll, fffff gggg),LOSS (ddddd, hhhhhh – i.e.),F TO G ( “F” is switching to “G”)

Output –

PAY - addition,FILES,DED,LOSS,F TO G

Upvotes: 2

Views: 43

Answers (2)

Luuk
Luuk

Reputation: 14958

Another on for MYSQL8.0:

SET @input:="PAY - addition,FILES (aaaaaaaaaaaaaa/bbbbbbbbbbbs i.e. ssss,ffff – i.e. cccccc),DED (ppppppp, llllll, fffff gggg),LOSS (ddddd, hhhhhh – i.e.),F TO G ( “F” is switching to “G”)";

with recursive cte as (
    select 
        0 i, 
        @input as text
    union all
    select 
        i+1, 
        CASE WHEN instr(text,'(') >0 AND instr(text,')')>instr(text,'(') THEN REPLACE(text, substring(text,instr(text,'('),instr(text,')')-instr(text,'(')+1), '') ELSE '' END
    from cte 
    where  i<10
) select text from cte where text<>'' order by i desc limit 1;

output:

+------------------------------------------+
| text                                     |
+------------------------------------------+
| PAY - addition,FILES ,DED ,LOSS ,F TO G  |
+------------------------------------------+
1 row in set (0.00 sec)

Upvotes: 0

GMB
GMB

Reputation: 222652

If you are running MySQL 8.0, you can do this with regexp_replace():

regexp_replace(mytext, '\\([^)]*\\)', '')

This works as long as there are no nested parentheses in the expression (which is consistent with your sample data).

Demo on DB Fiddle:

 select regexp_replace(
    'PAY - addition,FILES (aaaaaaaaaaaaaa/bbbbbbbbbbbs i.e. ssss,ffff – i.e. cccccc),DED (ppppppp, llllll, fffff gggg),LOSS (ddddd, hhhhhh – i.e.),F TO G ( “F” is switching to “G”)',
    '\\([^)]*\\)',
    ''
) val

| val                                      |
| :--------------------------------------- |
| PAY - addition,FILES ,DED ,LOSS ,F TO G  |

Upvotes: 3

Related Questions