POSIX ERE Regular expression to find repeated substring

Question

I have a set of strings containing a minimum of 1 and a maximum of 3 values in a format like this:

123;456;789
123;123;456
123;123;123
123;456;456
123;456;123

I'm trying to write a regular expression so I can find values repeated on the same string, so if you have 123;456;789 it would return null but if you had 123;456;456 it would return 456 and for 123;456;123 return 123

I managed to write this expression:

(.*?);?([0-9]+);?(.*?)\2

It works in the sense that it returns null when there are no duplicate values but it doesn't return exactly the value I need, eg: for the string 123;456;456 it returns 123;456;456and for the string 123;123;123 it returns 123;123

What I need is to return only the value for the ([0-9]+) portion of the expression, from what I've read this would normally be done using non-capturing groups. But either I'm doing it wrong or Oracle SQL doesn't support this as if I try using the ?: syntax the result is not what I expect.

Any suggestions on how you would go about this on oracle sql? The purpose of this expression is to use it on a query.

SELECT REGEXP_SUBSTR(column, "expression") FROM DUAL;

EDIT:

Actually according to https://docs.oracle.com/cd/B12037_01/appdev.101/b10795/adfns_re.htm

Oracle Database implements regular expression support compliant with the POSIX Extended Regular Expression (ERE) specification.

Which according to https://www.regular-expressions.info/refcapture.html

Non-capturing group is not supported by POSIX ERE

Answer 1

If you only have three substrings, then you can use a brute force method. It is not particularly pretty, but it should do the job:

select (case when val1 in (val2, val3) then val1
             when val2 = val3 then val2
        end) as repeated
from (select t.*,
             regexp_substr(col, '[^;]+', 1, 1) as val1,
             regexp_substr(col, '[^;]+', 1, 2) as val2,
             regexp_substr(col, '[^;]+', 1, 3) as val3
      from t
     ) t
where val1 in (val2, val3) or val2 = val3;

Answer 2

This answer describes how to select a matching group from a regex. So using that,

SELECT regexp_substr(column, '(\d{3}).*\1', 1, 1, NULL, 1) from dual;
#                                                       ^ Select group 1

Working demo of the regex (courtesy: OP).

Answer 3

Please bear with me and think of this different approach. Look at the problem a little differently and break it down in a way that gives you more flexibility in how you you are able look at the data. It may or may not apply to your situation, but hopefully should be interesting to keep in mind that there are always different ways to approach a problem.

What if you turned the strings into rows so you could do standard SQL against them? That way you could not only count elements that repeat but perhaps apply aggregate functions to look for patterns across sets or something.

Consider this then. The first Common Table Expression (CTE) builds the original data set. The second one, tbl_split, turns that data into a row for each element in the list. Uncomment the select that immediately follows to see. The last query selects from the split data, showing the count of how often the element occurs in the id's data. Uncomment the HAVING line to restrict the output to those elements that appear more than one time for the data you are after.

With the data in rows you can see how other aggregate functions could be applied to slice and dice to reveal patterns, etc.

SQL> with tbl_orig(id, str) as (
     select 1, '123;456;789' from dual union all
     select 2, '123;123;456' from dual union all
     select 3, '123;123;123' from dual union all
     select 4, '123;456;456' from dual union all
     select 5, '123;456;123' from dual
   ),
   tbl_split(id, element) as (
   select id,
          regexp_substr(str, '(.*?)(;|$)', 1, level, NULL, 1) element
   from tbl_orig
   connect by level <= regexp_count(str, ';')+1
   and prior id = id
   and prior sys_guid() is not null
   )
   --select * from tbl_split;
   select distinct id, element, count(element)
   from tbl_split
   group by id, element
   --having count(element) > 1
   order by id;

        ID ELEMENT     COUNT(ELEMENT)
---------- ----------- --------------
         1 123                      1
         1 456                      1
         1 789                      1
         2 123                      2
         2 456                      1
         3 123                      3
         4 123                      1
         4 456                      2
         5 123                      2
         5 456                      1

10 rows selected.

SQL>