10-348 cycle句で繰り返し防止

SQLパズル

StrTable
ID  Val
--  ------------------------------
 1  1,2,"3,4,5",6,7,"8,9,10,11",12
 2  1,2,"3",6,7,"8,9,10,11",12
 3  "3,4,5"
 4  8,9,10,11

ダブルコートで囲まれたカンマを半角空白に変換する。

出力結果
ID  Val
--  ------------------------------
 1  1,2,"3 4 5",6,7,"8 9 10 11",12
 2  1,2,"3",6,7,"8 9 10 11",12
 3  "3 4 5"
 4  8,9,10,11

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

データ作成スクリプト

create table StrTable(ID,Val) as
select 1,'1,2,"3,4,5",6,7,"8,9,10,11",12' from dual union
select 2,'1,2,"3",6,7,"8,9,10,11",12'     from dual union
select 3,'"3,4,5"'                        from dual union
select 4,'8,9,10,11'                      from dual;

SQL

col Val for a30

--■■■model句を使う方法(10g以降)■■■
select ID,Val
  from StrTable
 model
dimension by(ID)
measures(Val)
rules iterate(100)(
Val[any] = RegExp_Replace(Val[cv()],',(([^"]*"){2})*([^"]*"[^"]*)$',' \1\3'));

--■■■再帰with句を使う方法(11gR2以降)■■■
with rec(ID,Val) as(
select ID,Val
  from StrTable
union all
select ID,
RegExp_Replace(Val,',(([^"]*"){2})*([^"]*"[^"]*)$',' \1\3')
  from rec)
cycle ID,Val SET IsLoop TO 'Y' DEFAULT 'N'
select ID,Val
  from rec
 where IsLoop = 'Y'
order by ID;

--■■■表関数を使う方法■■■
create or replace Package PackTest Is
    type PrintType is record(
    ID  StrTable.ID%type,
    Val StrTable.Val%type);

    type PrintTypeSet is table of PrintType;
end;
/

create or replace function PrintR return PackTest.PrintTypeSet PipeLined IS
    WillOut varchar2(4000);
    InDoubleQuotation boolean;
    outR PackTest.PrintType;
begin
    for rec in (select ID,Val from StrTable order by ID) Loop
        WillOut := null;
        InDoubleQuotation := false;

        for I in 1..Length(rec.Val) Loop
            if substr(rec.Val,I,1) = '"' then
                InDoubleQuotation := not(InDoubleQuotation);
                WillOut := WillOut || substr(rec.Val,I,1);
            elsif substr(rec.Val,I,1) = ',' then
                if InDoubleQuotation then
                    WillOut := WillOut || ' ';
                else
                    WillOut := WillOut || substr(rec.Val,I,1);
                end if;
            else
                WillOut := WillOut || substr(rec.Val,I,1);
            end if;
        end Loop;

        outR.ID  := rec.ID;
        outR.Val := WillOut;
        pipe row(outR);
    end Loop;
end;
/

sho err

select * from table(PrintR);

解説

再帰with句のcycle句で繰り返しを防止できます。