MySQL remove string between brackets-CodePudding

How to remove characters between two brackets and brackets. For example

Let it [Am]be, let it [C/G]be, let it [F]be, let it [C]be
[C]Whisper words of [G]wisdom, let it [F]be [C/E] [Dm] [C]

I want above string to

Let it be, let it be, let it be, let it be
Whisper words of wisdom, let it be

I tried below code but it is only remove first brackets.

REPLACE(lyric, SUBSTRING(lyric, LOCATE('[', lyric), LENGTH(lyric) - LOCATE(']', 
REVERSE(lyric)) - LOCATE('[', lyric)   2), ''))

CodePudding user response：

MySQL 8.0 has a new function REGEXP_REPLACE().

Demo:

mysql> set @s = 'Let it [Am]be, let it [C/G]be, let it [F]be, let it [C]be';

mysql> select regexp_replace(@s, '\\[[^]]*\\]', '@') as result;
 ------------------------------------------------ 
| result                                         |
 ------------------------------------------------ 
| Let it @be, let it @be, let it @be, let it @be |
 ------------------------------------------------

I used '@' as the replacement text so it was more clear that the replacement worked multiple times. But you can replace the pattern with '' to just remove it.

If you're using an earlier version of MySQL that doesn't have this function, I recommend that you upgrade. MySQL 8.0 has been the current version since 2018.

If you can't upgrade yet, then I recommend you solve this by fetching the string into your client application, perform string substitutions, and then UPDATE the new string back into the database. This is inconvenient, but a SQL-only solution is also inconvenient.