Re: Remove duplicates from a field possible?

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Mon, 6 Mar 2006 17:56:53 +0100
Message-ID: <440c6957$0$21464$636a55ce@news.free.fr>

"Mark Milke" <mark_milke_at_yahoo.com> a écrit dans le message de news: 1141630427.043643.309240_at_u72g2000cwu.googlegroups.com...
| Hi all,
|
| we store in a var field values in the format 123; 345; 567; In some
| records the field contains duplicates, i.e. 123; 345; 123; 567; Is it
| possible to selelct those records somehow, or even better get rid of
| the duplicates?
|
| Mark
|

SQL> select * from t order by id;

        ID VALS

---------- ------------------------------
         1 123; 345; 123; 567;
         2 abc; cde; abc; efg;

2 rows selected.

SQL> def max_vals=10
SQL> col vals format a30
SQL> with
  2    step1 as (
  3      select id, vals, rn
  4      from t, (select rownum rn from dual connect by level <= &max_vals)

  5 ),
  6 step2 as (

  7      select distinct
  8             id,
  9             trim(substr(vals,
 10                         decode(rn,1,0,instr(vals,';',1,rn-1))+1,
 11                         instr(vals,';',1,rn)
 12                         -decode(rn,1,0,instr(vals,';',1,rn-1))-1)) val
 13      from step1
 14      where instr(vals,';',1,rn) > 0

 15 ),
 16 step3 as (

 17      select id, val,
 18             row_number () over (partition by id order by val) curr,
 19             row_number () over (partition by id order by val)-1 prev
 20      from step2

 21 )
 22 select id, max(substr(sys_connect_by_path(val,';'),2))||';' vals  23 from step3
 24 connect by prior id = id and prior curr = prev  25 start with prev = 0
 26 group by id
 27 /

        ID VALS

---------- ------------------------------
         1 123;345;567;
         2 abc;cde;efg;

2 rows selected.

Regards
Michel Cadot Received on Mon Mar 06 2006 - 10:56:53 CST