Oracle replace square brackets REGEX_REPLACE

Question

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?

Joakim Danielson · Accepted Answer · 2019-01-30 09:08:01Z

1

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;

edited Jan 30, 2019 at 9:08

answered Jan 30, 2019 at 9:01

Joakim Danielson

53.1k5 gold badges36 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Guillaume Deguines Over a year ago

It's working perfectly with the sample and on my whole table, thx ! Now, is there a difference, performance side, with your answer and the one of J.Lee, wich is working perfectly too ?

Joakim Danielson Over a year ago

@GuillaumeDeguines, I can't see that answer anymore, looks like it was deleted.

Guillaume Deguines Over a year ago

He was suggesting to do : SELECT REGEXP_REPLACE('BIG''EAST_?°[]', '[_?°''\[]|\]', ' ') FROM DUAL;

Joakim Danielson Over a year ago

@GuillaumeDeguines, I don't see any obvious reasons why one would be faster than the other since they are so similar.

Collectives™ on Stack Overflow

Oracle replace square brackets REGEX_REPLACE

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related