Search code examples
sqloracle-databaseregexp-replace

Oracle replace square brackets REGEX_REPLACE

I have strings in one of my table in which I need to replace some specials characters like ' _ ? ° and square brackets [ ]. When I try this it's working like expected : 

SELECT REGEXP_REPLACE('BIG''EAST_?°[]', '[_?°'']', ' ') FROM DUAL;

I get:

BIG EAST   []

Then I add the square brackets in my regex : 

SELECT REGEXP_REPLACE('BIG''EAST_?°[]', '[_?°''\[\]]', ' ') FROM DUAL;

I expected this: 

BIG EAST

But I get: 

BIG'EAST_?°

How can I properly escape the square brackets in my regex?

Solution

  • You need to add a * to match multiple occurrences (and in any order) of characters from your pattern

    SELECT REGEXP_REPLACE('BIG''EAST_?°[]', '[_?°''\[\]]*', ' ') FROM DUAL;