Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Using regular expression to find any recurring substring within string

649496Jul 10 2008 — edited Jul 15 2008
I'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.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 12 2008
Added on Jul 10 2008
9 comments
7,904 views