10-59 カンマ区切りの文字列の編集

SQLパズル

v_char_type constant varchar2(32) := 'AB,BC,CD,DE,EF,FG,GH,HI,JK,OP,QR';
v_num_type  constant varchar2(34) := '10,20,30,40,50,60,70,80,90,100,110';

PL/SQLで、上記の変数を元に、
以下の出力を行う

出力結果
--------
AB 10
BC 20
CD 30
DE 40
EF 50
FG 60
GH 70
HI 80
JK 90
OP 100
QR 110

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

SQL

--■■■正規表現を使う方法(10g以降)■■■
declare
    v_char_type constant varchar2(32) := 'AB,BC,CD,DE,EF,FG,GH,HI,JK,OP,QR';
    v_num_type  constant varchar2(34) := '10,20,30,40,50,60,70,80,90,100,110';
begin
    for rec_Work in
    (select
     RegExp_Replace(v_char_type,'^([^,]+,){' || to_char(RowNum-1) || '}([^,]+).*$','\2') as v_char,
     RegExp_Replace(v_num_type ,'^([^,]+,){' || to_char(RowNum-1) || '}([^,]+).*$','\2') as v_num
       from all_catalog
      where RowNum <= Length(v_char_type) - Length(Replace(v_char_type,','))+1) Loop

        DBMS_Output.Put_Line(rec_Work.v_char || ' ' || rec_Work.v_num);
    end Loop;
end;
/

--■■■instr関数を使う方法■■■
declare
    v_char_type constant varchar2(32) := 'AB,BC,CD,DE,EF,FG,GH,HI,JK,OP,QR';
    v_num_type  constant varchar2(34) := '10,20,30,40,50,60,70,80,90,100,110';
begin
    for rec_Work in
    (select
    substr(
    v_char_type,
    decode(RowNum,1,1,instr(v_char_type,',',1,RowNum-1)+1),
    case when instr(v_char_type,',',decode(RowNum,1,1,instr(v_char_type,',',1,RowNum-1)+1)) > 0
         then instr(v_char_type,',',decode(RowNum,1,1,instr(v_char_type,',',1,RowNum-1)+1))
             -decode(RowNum,1,1,instr(v_char_type,',',1,RowNum-1)+1)
         else Length(v_char_type) end) as v_char,
    substr(
    v_num_type,
    decode(RowNum,1,1,instr(v_num_type,',',1,RowNum-1)+1),
    case when instr(v_num_type,',',decode(RowNum,1,1,instr(v_num_type,',',1,RowNum-1)+1)) > 0
         then instr(v_num_type,',',decode(RowNum,1,1,instr(v_num_type,',',1,RowNum-1)+1))
             -decode(RowNum,1,1,instr(v_num_type,',',1,RowNum-1)+1)
         else Length(v_num_type) end) as v_num
  from all_catalog
 where RowNum <= Length(v_char_type) - Length(Replace(v_char_type,','))+1) Loop

        DBMS_Output.Put_Line(rec_Work.v_char || ' ' || rec_Work.v_num);
    end Loop;
end;
/

解説

正規表現を使うと、文字列の編集がシンプルになります

---