CODEWITHSUNDEEP
oracle-databaseregexp-replace
Pablo
Pablo

Reputation: 13

regex_replace string between

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.

Upvotes: 1

Views: 6024

Answers (3)

BobC
BobC

Reputation: 4432

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.

Upvotes: -1

Aleksej
Aleksej

Reputation: 22969

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

Upvotes: 4

Patrick Murphy
Patrick Murphy

Reputation: 2329

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).

Upvotes: 0

Related Questions