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?

What if there are multiple repetitions? What with things like aaaaa? Is that aa twice followed by a (resulting in aaa) or is it a repeated 5 times, resulting in a? — Martin Ender
– Martin Ender, Commented Aug 27, 2013 at 19:19
@m.buettner There are not multiple repetitions. The "string that repeats" starts right after the static text and repeats exactly once. — Jeremy
– Jeremy, Commented Aug 27, 2013 at 19:40

Egor Skriptunoff · Accepted Answer · 2013-08-27 19:52:42Z

7

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

fiddle

answered Aug 27, 2013 at 19:52

Egor Skriptunoff

23.8k2 gold badges38 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kol Over a year ago

+1 This solution also works with PostgreSQL: sqlfiddle.com/#!1/85101/1

user1028435 · Accepted Answer · 2013-08-27 20:19:12Z

3

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.

answered Aug 27, 2013 at 20:19

user1028435

3093 silver badges11 bronze badges

Comments

Fast Old Man · Accepted Answer · 2013-08-27 19:36:55Z

0

It seems to me that you might be pushing SQL beyond what it is capable/designed for. Is it possible for you to handle this situation programmatically in the layer that lays under the data layer where this type of thing can be more easily handled?

answered Aug 27, 2013 at 19:36

Fast Old Man

1053 silver badges13 bronze badges

Comments

kol · Accepted Answer · 2013-08-27 20:01:50Z

0

The REPLACE function should be enough 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

AFAIK the REPLACE function is not 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.

edited Aug 27, 2013 at 20:01

answered Aug 27, 2013 at 19:54

kol

28.9k13 gold badges91 silver badges126 bronze badges

2 Comments

Jerry Over a year ago

You're hardcoding the repeating string here, while it could be any repeating string.

kol Over a year ago

@Jerry I see. So REPLACE is enough only when the there are only a small number of possible strings which can be the repeated substring, and all of them are known in advance.

Collectives™ on Stack Overflow

Oracle SQL -- remove partial duplicate from string

4 Answers 4

1 Comment

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related