how to remove/replace a substring like match to the expression like %[%]% from a column

Question

I have a column message which have expression like [ example] or [bla bla]. Basically, I want to remove braces [] as well as all text between the braces.
For example:

input: its a string [text to remove including braces] to replace.
output: its a string to replace

I have tried alot of solutions, but still not working

UPDATE resource SET message = REPLACE(message,'%[%]%','')

Please, provide DBMS you're using and tag your question with it. SQL is just a language. — Pavel Smirnov, Nov 11 '19 at 07:58
i am using mysql with phpmyadmin. its an sql language question so i taged only sql — Muhammad Usman akram, Nov 11 '19 at 08:04

Pavel Smirnov · Answer 1 · 2019-11-11T08:31:05.467

0

Have a look at REGEXP_REPLACE function. You can provide a pattern to match a substring you like.

Here is an example:

select regexp_replace('its a string [text to remove including braces] to replace.', 
                      '\\[[^\\]]*\\]', -- the pattern
                      '');

Output:

its a string to replace.

edited Nov 11 '19 at 08:31

answered Nov 11 '19 at 08:19

Pavel Smirnov

4,611
3
18
28

@MuhammadUsmanakram, which version of MySQL are you using? And what is the error exactly? – Pavel Smirnov Nov 11 '19 at 16:45
mysql Ver 14.14 Distrib 5.7.27, for Linux (x86_64) using EditLine wrapper – Muhammad Usman akram Nov 12 '19 at 05:50
#1064 you have an error in syntax; check manual for version for the right syntax to use near '(' its a string [text to remove including brace] to replace.', at line 1. – Muhammad Usman akram Nov 12 '19 at 05:57
i used my table name between select and regexp_replace – Muhammad Usman akram Nov 12 '19 at 05:57
@MuhammadUsmanakram, `REGEXP_REPLACE` function is available since version 8.0. So if possible, upgrade to a newer version of MySQL. If not, you may want to create a user-defined function. In this case look at the answers [here](https://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql) to get more details. – Pavel Smirnov Nov 12 '19 at 06:44

Gordon Linoff · Answer 2 · 2019-11-11T17:07:39.640

0

If you have only one pair of square braces, you can use:

UPDATE resource
    SET message = CONCAT(SUBSTRING_INDEX(message, '[', 1),
                         SUBSTRING_INDEX(message, ']', -1)
                        )
    WHERE message LIKE '%[%]%';

edited Nov 11 '19 at 17:07

answered Nov 11 '19 at 12:28

Gordon Linoff

1,242,037
58
646
786

thanks Gordan Linoff. but it does not working well. left brace remains in string. and sometime it removes all data after left brace – Muhammad Usman akram Nov 11 '19 at 16:14

how to remove/replace a substring like match to the expression like %[%]% from a column

2 Answers2