Oracle Database Discussions

there was the same topic when the OP wanted to delete duplicates from string like AAABBBAAABBBCCC

but unfortunately - there're some problems with the search engine on that forum, so I can't find it :(
it was about 3 days ago.

You mentioned this thread.
569585

Actually,
This solution needs to arrange.
Because '|' is separate.

declare
    Str varchar2(4000) := '1234|5678|1234|679134|1235|1234|679134|5678|1234|8888|888|88|888|';
begin
    DBMS_Output.Put_Line(Str);
    for i in 1..length(Str) Loop
      --Str := RegExp_Replace(Str,'(^|\|)([^|]+\|)(.*?\|)\2','\1\2\3');
        Str := RegExp_Replace(Str,'(^|\|)([^|]+\|)(.*\|)\2','\1\2\3');
    end Loop;
    DBMS_Output.Put_Line(Str);
end;
/
1234|5678|1234|679134|1235|1234|679134|5678|1234|8888|888|88|888|
1234|5678|679134|1235|8888|888|88|

Hi Keith Jamieson.
You can make solution which you want.
You can use above Regex in Model or TableFunction or Function.

You mean this 569585?

C.

yep, guys, but how did you find it?
seems that my search isn't working even now, when I put 'AAABBBCCCBBBDDDAAAEEEBBB' in the search string ((

Just looked at my profile, it's still in the list of the last 10 posts. ;-)

C.

oh, in mine it's not in ten last((
but when you look all your messages - they are sorted not by date - and I can't sort them by date.
it's really inconvinient sometimes.

Thanks Guys ,
Thats perfect.
I have run a sample test on my data and it works perfectly.
I can now run an update (a few times, but thats better than the pl/sql alternative).

I do not fully understand the syntax so will investigate it. I can sort of see whats happening.

select RegExp_Replace(Str,'(^|\|)([^|]+\|)(.*\|)\2','\1\2\3') str
from
(
select RegExp_Replace(Str,'(^|\|)([^|]+\|)(.*\|)\2','\1\2\3') str
from
(
select RegExp_Replace(Str,'(^|\|)([^|]+\|)(.*\|)\2','\1\2\3') str
from
(
select RegExp_Replace(Str,'(^|\|)([^|]+\|)(.*\|)\2','\1\2\3') str
from
(
 select RegExp_Replace(Str,'(^|\|)([^|]+\|)(.*\|)\2','\1\2\3') str
from
(
WITH BARCODE AS
(SELECT '1234|5678|1234|679134|1235|1234|679134|5678|1234|'   str
FROM DUAL)
--AS SELECT * FROM  DUAL
--SELECT bar,instr(bar,'|'),instr(bar,'|')  FROM BARCODE
 select RegExp_Replace(Str,'(^|\|)([^|]+\|)(.*\|)\2','\1\2\3') str
        from barcode
)  str
)
)
)
)

I did an 429716 a while ago, which could help you understand those "cryptic" string patterns.

C.

Small problem when ran against actual data.
I'm ending up with a string as follows,
which as you can see, there should only be one occurance of as it's a repeat string
24139000191161|24139000191161|

Wow!
Regex which I created is wrong.
This is correct regex.

declare
    Str1 varchar2(4000) := '1234|5678|1234|679134|1235|1234|679134|5678|1234|8888|888|88|888|';
    Str2 varchar2(4000) := '24139000191161|24139000191161|';
begin
    DBMS_Output.Put_Line(Str1);
    for i in 1..length(Str1) Loop
        Str1 := RegExp_Replace(Str1,'(^|\|)([^|]+\|)(.*\|)?\2','\1\2\3');
    end Loop;
    DBMS_Output.Put_Line(Str1);

    DBMS_Output.Put_Line(Str2);
    for i in 1..length(Str2) Loop
        Str2 := RegExp_Replace(Str2,'(^|\|)([^|]+\|)(.*\|)?\2','\1\2\3');
    end Loop;
    DBMS_Output.Put_Line(Str2);
end;
/
1234|5678|1234|679134|1235|1234|679134|5678|1234|8888|888|88|888|
1234|5678|679134|1235|8888|888|88|
24139000191161|24139000191161|
24139000191161|

By the way,
I think that this regex book is very good. http://www.oreilly.com/catalog/regex3/
I have second edition of Japanese language version.

Yup, that fixed it and CD's page bookmarked.

I think I need to buy that book.

For your easier investigation - I posted my first note in a blog:

http://volder-notes.blogspot.com/