Regexp_replace : Remove dupliactes from '+' dilimited string in Oracle

Question

I have a string in a table column delimited by '+'. I need to remove the duplicates.

Illustration :

My data:

a+a+a+b+b+c+c

Expectation:

a+b+c

I tried with REGEXP_REPLACE but not able to escape the + character as it is reserved.

I am successful with comma separated value but need help with + or ? separated values.

Working Code with comma separated value:

SELECT REGEXP_REPLACE ('a,a,a,b,b,c,c', '([^,]+)(,\1)+', '\1') FROM DUAL;

have you tried escaping +? like : ('a+a+a+b+b+c+c', '([^\+]+)(+\1)+', '\1') — marco, Oct 30 '17 at 13:33
Fix your data structure. You shouldn't be storing lists as strings, especially when Oracle offers many alternatives. — Gordon Linoff, Oct 30 '17 at 13:44
This regular expression does not work when the repeating items are not consecutive. It also does not work if you have `ba+a` as it will match the `a` in `ba` and remove the unique `a` term that should be kept. — MT0, Oct 30 '17 at 13:56
@MT0 : That's a good point.. Thanks. but the solution works fine for my situation. — Bikash Ranjan Bhoi, Oct 31 '17 at 13:43

score 1 · Accepted Answer · answered Oct 30 '17 at 13:44

1

If you need to use a reserved character as its normal value you need to escape it with a backslash. This works for all reserved characters.

You can modify your existing code by replacing each comma in the regex with...

\+

For example:

SELECT REGEXP_REPLACE('a+a+a+b+b+c+c', '([^\+]+)(\+\1)+', '\1') FROM DUAL;

answered Oct 30 '17 at 13:44

Matthew Hart

marco · Answer 2 · 2017-11-11T17:44:55.270

0

just escape the +:

 SELECT REGEXP_REPLACE ('a+a+a+b+b+c+c', '([^\+]+)(\+\1)+', '\1') FROM DUAL;

edited Nov 11 '17 at 17:44

answered Oct 30 '17 at 13:43

marco

1

You need to use back-slash, not forward-slash to escape – Matthew Hart Oct 30 '17 at 13:45

2 Answers2