Oracle SQL -- remove partial duplicate from string

Question

I have a table with a column with strings that looke like this:

static-text-here/1abcdefg1abcdefgpxq

From this string 1abcdefg is repeated twice, so I want to remove that partial string, and return:

static-text-here/1abcdefgpxq

I can make no guarantees about the length of the repeat string. In pure SQL, how can this operation be performed?

Answer 1

regexp_replace('static-text-here/1abcdefg1abcdefgpxq', '/(.*)\1', '/\1')

fiddle

Answer 2

If you can guarantee a minimum length of the repeated string, something like this would work:

select REGEXP_REPLACE
   (input, 
   '(.{10,})(.*?)\1+', 
   '\1') "Less one repetition" 
   from tablename tn where ...;

I believe this can be expanded to meet your case with some cleverness.