0

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
  1. Remove @DATA: from the beginning of string
  2. Remove @ at the end of string
  3. Replace : with space
Vishal
  • 198
  • 1
  • 3
  • 11
  • https://stackoverflow.com/questions/48924687/search-a-string-for-a-regular-expression-pattern-and-replace-each-occurences-as/48925355?noredirect=1#comment84858524_48925355 – Nwn Feb 23 '18 at 06:21

2 Answers2

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);
Michael Piankov
  • 1,989
  • 1
  • 8
  • 19
  • Thanks, it worked. I was struggling with the sub expression. I tried lot of combos.. but this was it, which I missed :) – Vishal Oct 18 '16 at 07:27
  • Also, I tried another pattern - **@DATA:(.+?):(.+?)@**. Are there any repercussions with this? – Vishal Oct 18 '16 at 11:41
  • 1
    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' )` – Michael Piankov Oct 18 '16 at 11:58
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);
schurik
  • 7,798
  • 2
  • 23
  • 29