Skip to Main Content

SQL & PL/SQL

Announcement

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.

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
8,641 views