regex_replace string between

Question

I'm not expert in a regular expressions, and in oracle I want to find a string in a text using regexp_replace oracle function.

The string to find has at beginning an "{" and at the end an "}".
Between "{" and "}", you will find letters and "_" characters.

So, if I have this text:

this is a {HI_FRIEND} test to replace

how can I erase the string "{HI_FRIEND}"?

I tried this:

select REGEXP_REPLACE('this is a {HI_FRIEND} test to replace','*{(A-Z-)}*','') from dual

but it's not working.

The field that contains the text is in a table with 1 millions of records at least.

Answer 1

Give this a try:

select REGEXP_REPLACE('this is a {HI_FRIEND} test to replace','{(.*?)}') from dual

this wil replace strings wrapped by {}, no matter their content.

The lazy operator (?) is used to avoid issues in case more than one wrapped string appears.

For example:

select REGEXP_REPLACE('this is a {HI_FRIEND} test to {HI_FRIEND} replace','{(.*)}') from dual

gives

this is a  replace

while with the lazy operator we have:

select REGEXP_REPLACE('this is a {HI_FRIEND} test to {HI_FRIEND} replace','{(.*?)}') from dual

and the result:

this is a  test to  replace

If you only want to remove wrapped strings when they are formed by capital letters and '_', you can edit (.*?) into ([A-Z_]*?):

select REGEXP_REPLACE('this is a {HI_FRIEND} test to {123} replace','{([A-Z_]*?)}') from dual

will give:

this is a  test to {123} replace