How to remove text within brackets from a text in SQL with REGEXP_REPLACE

For a project I’m currently working on I had to remove the filter option from an xpath expression to get the path without filters. In order to do that I used the Oracle function REGEXP_REPLACE which is very powerful for this kind of things.

So if you have a text like below:

Hi, this is text between brackets[element=name] and another one[element=nameagain]

…and want to remove the text parts within the [..] including the brackets itself issue the below in SQL for your column with the text. The same applies also for pl/sql code.

SELECT REGEXP_REPLACE('Hi, this is text between brackets[element=name] and another one[element=nameagain]','\[.+?\]') FROM DUAL;

will result in the below:

Hi, this is text between brackets and another one

or in Pl/SQL:

l_text := 'Hi, this is text between brackets[element=name] and another one[element=nameagain]';
l_text := REGEXP_REPLACE(l_text,'\[.+?\]');

The key here is of course the regular expression [.+?] which will find a part of text surrounded by brackets. Enjoy!