For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.
with t1 as (select '!x!y!z!y!' str from dual union all select '! str_1 ! str_2 ! str_3 ! str_1 !' from dual union all select '! str ! str_A ! str_B ! _B !' from dual ) select str from t1 where regexp_like(str, '(![^!]+!).*\1')
How about this. It works with your test data but I'm not sure about your real data.
with t1 as (select '!x!y!y!z!' str from dual union all select '! str_1 ! str_2 ! str_3 ! str_1 !' from dual union all select '! str ! str_A ! str_B ! _B !' from dual ) select str from t1 where regexp_like(str, '(![^!]+!).*\1') OR regexp_like(str, '(!?[^!]+!)\1')
Sorry but I'm determined to solve your issue without you having to do unnecessary replaces. How bout this one:
with t1 as (select '!xy!y!z!' str from dual union all select '! str_1 ! str_2 ! str_3 ! str_1 !' from dual union all select '! str ! str_A ! str_B ! _B !' from dual ) select str from t1 where regexp_like(str, '(![^!]+!).*\1') OR regexp_like(str, '(!)([^!]+)(!)\2\3')
Regexs can be tricky but once you get the hang of them, they are a priceless tool.
with t1 as (select '!x!y!z!y!' str from dual union all select '! str_1 ! str_2 ! str_3 ! str_1 !' from dual union all select '! str ! str_A ! str_B ! _B !' from dual) select str from t1 where RegExp_Like(str,'(![^!]+).*\1(!|$)');
STR --------------------------------- !x!y!z!y! ! str_1 ! str_2 ! str_3 ! str_1 !
or
with t1 as (select '!x!y!z!y!' str from dual union all select '! str_1 ! str_2 ! str_3 ! str_1 !' from dual union all select '! str ! str_A ! str_B ! _B !' from dual) select str from t1 where RegExp_Like(str,'(![^!]+).*\1!');
Oh, I see. My final solution which is similar your solution.
col match for a20
with t1 as (select 'x!x' str from dual union all select 'x!x!y!z' str from dual union all select 'y!x!x!z' from dual union all select 'y!z!x!x' from dual union all select 'x!y!x!z' from dual union all select 'y!x!z!x' from dual union all select 'w!x!y!x!z' from dual union all select 'str1!str1!str2' from dual union all select 'str1!str2!str1' from dual union all select 'str1!str2!str3!str2!str4' from dual union all select 'w!x!y!z' from dual union all select 'x!str1!str!1!tr!str2!_str2!2!str2_!str3!3' from dual union all select 'AAAA!x!y' from dual union all select 'AAAA!AAA!y' from dual) select str,RegExp_Substr(str,'(^|!)([^!]+)!(.+!)?\2(!|$)') as match from t1 WHERE RegExp_Like(str,'(^|!)([^!]+)!(.+!)?\2(!|$)');
STR MATCH ------------------------ ------------------- x!x x!x x!x!y!z x!x! y!x!x!z !x!x! y!z!x!x !x!x x!y!x!z x!y!x! y!x!z!x !x!z!x w!x!y!x!z !x!y!x! str1!str1!str2 str1!str1! str1!str2!str1 str1!str2!str1 str1!str2!str3!str2!str4 !str2!str3!str2!