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.

what if I have a string like 'something {123} something else'? Should I remove it or not? — Aleksej
– Aleksej, Commented Jan 18, 2017 at 0:42
In my case, I must to delete all characters between "{" and "}". You first example was perfect because replace the complete content. — Pablo
– Pablo, Commented Jan 18, 2017 at 12:56

Aleksej · Accepted Answer · 2017-01-18 00:41:42Z

4

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

edited Jan 18, 2017 at 0:41

answered Jan 18, 2017 at 0:36

Aleksej

23.1k6 gold badges38 silver badges41 bronze badges

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

Comments

Patrick Murphy · Accepted Answer · 2017-01-18 00:40:07Z

0

A great tool for regex, as I always have these issues myself is regex101.com

You can enter your regex, and your sample data and see what matches, it also puts in plain english what the regex is looking for, as well as providing reference to syntax.

Try the following regex {([A-Z_])*}

It matches { and } litterally, but then the charactor class [A-Z_] (Capital A to Capital Z or underscore) * times (between 0 and unlimited times).

answered Jan 18, 2017 at 0:40

Patrick Murphy

2,33916 silver badges17 bronze badges

Comments

BobC · Accepted Answer · 2017-01-18 00:47:39Z

-1

I suspect that your problem is not so much using the regexp, but rather trying to update 1 million rows. I would suggest that you create a new table with the data you want, using the REGEXP answer posted. Something like...

create table new_table
as
select * from old_table
where 1=2
/

Then you have options to speed this up, by using a direct path load and parallelism

alter session enable parallel dml;

insert /*+ append */ into new_table( col1, col2, text_col, ... )
select col1, col2, REGEXP_REPLACE(...), ... )
from old_table
;

The drop the old table, rebuild any constraints indexes, regather stats and you're good to go. This will be much faster than an update.

answered Jan 18, 2017 at 0:47

BobC

4,4421 gold badge14 silver badges16 bronze badges

2 Comments

Migs Over a year ago

the OP did not specify that he's having a performance problem and the provided solution assumes that he has DDL privileges.

BobC Over a year ago

@MigsIsip. You are correct. However, the OP did state that he has at least 1M rows in the table. If the problem was only about string replacement, then ths fact would not be relevant. So, I provided what I believe to be useful information, both for the OP and other readers. If that is not applicable, then that is fine; hopefully it will be useful to someone in the future.

Collectives™ on Stack Overflow

regex_replace string between

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related