I have a string variable - varchar2(240) maximum - which is arbitarily filled with 3 letter codes. Any one or more of these codes may be duplicated within the string. What I need to do is to strip out all the duplications and end up with a single contiguous string containing all the unique code options. e.g.

Initial string 'AAABBBCCCBBBDDDAAAEEEBBB'

should end up as :-

'AAABBBCCCDDDEEE'

Hope this makes sense - also there is no need to place the unique codes in any kind of order.

Thanks for your help...

sorry - PL/SQL

Nice. The other way that springs to mind is to parse into nested table then use SET collection method to eliminate duplicates.

This may be a lengthier way, but it yields results(assuming you only have 3 same letter contiguous characters in your string)

xopmaster@XOPUAT> WITH mytab AS

  2  (SELECT 'AAABBBCCCBBBDDDAAAEEEBBB' mycol FROM DUAL)

  3  SELECT REVERSE(REPLACE(MAX(SYS_CONNECT_BY_PATH(mystr,'/')),'/','')) output_str

  4  FROM

  5  (

  6  SELECT mystr, rownum rn FROM

  7  (

  8  SELECT distinct substr(mycol, ((level-1) * 3) + 1,3) mystr FROM  mytab

  9  CONNECT BY LEVEL <= LENGTH(mycol) / 3

 10  )

 11  )

 12  connect by rn + 1 = prior rn

 13  ;



OUTPUT_STR



AAABBBCCCDDDEEE

another way to do it with MODEL (less iterations, less measures):

SQL> WITH t AS (SELECT 'AAABBBCCCBBBDDDAAAEEEBBB' str
  2               FROM dual
  3            )
  4            select str from t
  5             model
  6              dimension by (0 dim)
  7              measures(str)
  8              rules iterate(100) until (str[0] = previous(str[0]))
  9            (str[0]=regexp_replace(str[0],'(([[:alpha:]])\2{2})(.*?)\1','\1\3'));
 
STR
------------------------
AAABBBCCCDDDEEE
 
SQL>

before
AAABBBCCCBBBDDDAAAEEEBBB
after
AAABBBCCCDDDEEE

If you can use Editor,
many times

replace

((.)(?=(\2+))\3((?!\2).)+)\2+

to

\1

If you can use JavaStoredProcedure,
many times

replace

((.)\2++((?!\2).)+)\2+

to

\1

Possessive quantifiers
http://java.sun.com/j2se/1.4.2/docs/api/java/util/regex/Pattern.html

sorry
I took more mistake.

before
AAABBBCCCBBBDDDAAAEEEBBB
after
AAABBBCCCDDDEEE

before
AAABBBCCCCCCDDDDDDEEEDDDEEEDDD
after
AAABBBCCCDDDEEE

many times

replace

((.)\2{2}(.*?))\2+

to

\1