Using regular expression to find any recurring substring within string
649496Jul 10 2008 — edited Jul 15 2008I'm trying to come up with a regular expression to identify fields where a text string, which is made up of a set of substrings delimited by ! , contains a repeated substring. For example, the expression should match:
!x!y!z!y!
and:
! str_1 ! str_2 ! str_3 ! str_1 !
but not:
! str ! str_A ! str_B ! _B !
Something like the following REGEXP_LIKE expression with a back reference would work if the substrings were a fixed length and I knew how far apart the duplicate substrings were:
SELECT
(CASE WHEN
REGEXP_LIKE('!a!b!c!b!', '(!.!).\1')
THEN 'TRUE'
ELSE 'FALSE' END)
FROM DUAL;
But the substrings can be of any length, and duplicates can be separated by any number of substrings (or none), and using asterisks in place of the periods doesn't work: REGEXP_LIKE('!a!b!c!b!', '(*)*\1')
Is there a way to do this with regular expressions, or will I have to use a PL/SQL block loop through each string and separate out the substrings?
Thanks.