Oracle REGEXP_REPLACE - Replace multiple occurrences of the pattern with a substring

Question

Is it possible to replace multiple occurrences of the pattern with a substring using REGEXP_REPLACE function in Oracle. I tried multiple permutation & combination of the regex expression, but somehow it didn’t work out correctly. Superficially it looks doable but not very sure.. any ideas or pointer to try out. Sample input/output below

INPUT_STRING =  SELECT @DATA:T.ID:ID@, @DATA:T.NAME:NAME@, @DATA:T.ADDRESS:ADDRESS@, @DATA:T.CREATED_DATE:CREATED_DATE@ FROM TABLE_NAME T
OUTPUT_STRING = SELECT T.ID ID, T.NAME NAME, T.ADDRESS ADDRESS FROM TABLE_NAME T

In essence, we need to achieve below in the fixed pattern

@DATA:xx.yy:zz@, where xx is table, yy is column name, zz column alias

Remove @DATA: from the beginning of string
Remove @ at the end of string
Replace : with space

stackoverflow.com/questions/48924687/…

Nwn
– Nwn

2018-02-23 06:21:49 +00:00
Commented Feb 23, 2018 at 6:21 — Nwn
– Nwn, Commented Feb 23, 2018 at 6:21

Michael Piankov · Accepted Answer · 2016-10-17 12:10:00Z

1

I think you want something like

select regexp_replace(txt,'@DATA:(\w+\.(\w+)):(\2)@', '\1 \2' ) from ( 
   select 'SELECT @DATA:T.ID:ID@, @DATA:T.NAME:NAME@, @DATA:T.ADDRESS:ADDRESS@, @DATA:T.CREATED_DATE:CREATED_DATE@ FROM TABLE_NAME T' as txt 
   from dual);

answered Oct 17, 2016 at 12:10

Michael Piankov

1,9971 gold badge11 silver badges20 bronze badges

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

3 Comments

Vishal Over a year ago

Thanks, it worked. I was struggling with the sub expression. I tried lot of combos.. but this was it, which I missed :)

Vishal Over a year ago

Also, I tried another pattern - @DATA:(.+?):(.+?)@. Are there any repercussions with this?

Michael Piankov Over a year ago

Well, It depends of what you mean as repercussions. you specify good pattern ".+?" - will try to find "string" with least length and you may use it. with the same query regexp_replace(txt,'@DATA:(.+?):(.+?)@', '\1 \2' )

schurik · Accepted Answer · 2016-10-17 11:50:42Z

0

somthing like this:

 replace(regexp_replace(str, '^@DATA:|@$'), ':', ' ')

Test:

select str, replace(regexp_replace(str, '^@DATA:|@$'), ':', ' ') 
from ( select '@DATA:xx.yy:zz@' as str from dual);

answered Oct 17, 2016 at 11:50

schurik

7,9582 gold badges26 silver badges30 bronze badges

3 Comments

Michael Piankov Over a year ago

^ - is begin of the string $ - is the end. If it in middle of line it will not work

schurik Over a year ago

correct, but OP wanted to remove only from the begin and the end

Vishal Over a year ago

Thanks for your suggestion, it worked for the given pattern, however, when I am passing the entire string, it's not giving expected output.

Collectives™ on Stack Overflow

Oracle REGEXP_REPLACE - Replace multiple occurrences of the pattern with a substring

2 Answers 2

3 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related