10-78 文字の重複を削除

SQLパズル

dt_test_dumpテーブル
dcode
------------
AAABBCCDDEEA
DHSGHDADSFDF
BBDDDFFAACC

文字の重複を削除して出力する

出力結果
dcode         dcode_no_repeats
------------  ----------------
AAABBCCDDEEA  ABCDE
BBDDDFFAACC   BDFAC
DHSGHDADSFDF  DHSGAF

こちらを参考にさせていただきました(英語)

データ作成スクリプト

create table dt_test_dump(dcode varchar2(12));

insert into dt_test_dump values('AAABBCCDDEEA');
insert into dt_test_dump values('DHSGHDADSFDF');
insert into dt_test_dump values('BBDDDFFAACC');
commit;

SQL

col dcode for a12
col dcode_no_repeats for a15

select dcode,replace(max(sys_connect_by_path(str,',')),',') as dcode_no_repeats
from (select dcode,str,Row_Number() over(partition by dcode order by Counter) as Rank
      from (select a.dcode,b.Counter,
            substr(a.dcode,b.Counter,1) as str,
            min(b.Counter) over(partition by a.dcode,substr(a.dcode,b.Counter,1)) as minCounter
              from dt_test_dump a,(select RowNum as Counter from all_catalog) b
            where b.Counter <= Length(a.dcode))
      where Counter = minCounter)
start with Rank = 1
connect by prior dcode = dcode
       and prior Rank  = Rank-1
group by dcode
order by dcode;

--■■■model句を使う方法(10g以降)■■■
select dcode,dcode_no_repeats
  from dt_test_dump
 model
 dimension by (RowNum as soeji)
 measures(dcode,dcode as dcode_no_repeats)
 rules ITERATE (100) (dcode_no_repeats[any] = RegExp_Replace(dcode_no_repeats[CV()],'(.)(.*)\1','\1\2'))
order by dcode;

解説

重複を削除してから、階層問い合わせをしてます。

---