Oracle SQL - removing a partial duplicate from a row

Question

I have a table with a column with rows that look like this:

static-text-here/1abcdefg1abcdefgpxq 

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

 static-text-here/1abcdefgpxq 

I can not guarantee any guarantees regarding the length of the repeat line. In pure SQL, how can this operation be performed?

Answer 1

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

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

I believe this can be expanded to suit your case with some dexterity.

Answer 2

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

fiddle

Answer 3

It seems to me that you can force SQL to exceed what it is capable / intended for. Is it possible for you to handle this situation programmatically in a layer that lies beneath the data layer, where this type of thing can be more easily handled?

Answer 4

The REPLACE function should be sufficient to solve the problem.

Test table:

 CREATE TABLE test (text varchar(100)); INSERT INTO test (text) VALUES ('pxq'); INSERT INTO test (text) VALUES ('static-text-here/pxq'); INSERT INTO test (text) VALUES ('static-text-here/1abcdefgpxq'); INSERT INTO test (text) VALUES ('static-text-here/1abcdefg1abcdefgpxq'); 

Query:

 SELECT text, REPLACE(text, '1abcdefg1abcdefg', '1abcdefg') AS text2 FROM test; 

Result:

 TEXT TEXT2 pxq pxq static-text-here/pxq static-text-here/pxq static-text-here/1abcdefgpxq static-text-here/1abcdefgpxq static-text-here/1abcdefg1abcdefgpxq static-text-here/1abcdefgpxq 

The AFAIK REPLACE function is not available in the SQL99 standard, but most DBMSs support it. I tested it here and it works with MySQL, PostgreSQL, SQLite, Oracle and MS SQL Server.