10-40 文字列の部分一致で結合

SQLパズル

GroupTable
GroupID  SelectCode
-------  ----------
G1       1000
G2       1001
G3       1002
G4       1003
G5       1004
G6       9
G7       %%

UserTable
UserID   SelectKey
------   ------------------------
U1       1000,1001,1003
U2       1000,1001,1003,1004
U3       1000,1001,1003,1005,1006
U4       9

上の2つのテーブルを内部結合し、
UserTableのSelectKeyのデータをカンマ区切りで分けて、
それぞれと一致するGroupTableのGroupIDを出力する。

出力結果
UserID  GroupID
------  --------
U1      G1
U1      G2
U1      G4
U2      G1
U2      G2
U2      G4
U2      G5
U3      G1
U3      G2
U3      G4
U4      G6

データ作成スクリプト

create table GroupTable(
GroupID    char(2),
SelectCode char(4),
primary key (GroupID));

insert into GroupTable values('G1','1000');
insert into GroupTable values('G2','1001');
insert into GroupTable values('G3','1002');
insert into GroupTable values('G4','1003');
insert into GroupTable values('G5','1004');
insert into GroupTable values('G6','9');
insert into GroupTable values('G7','%%');

create table UserTable(
UserID    char(2),
SelectKey varchar2(64),
primary key (UserID));

insert into UserTable values('U1','1000,1001,1003');
insert into UserTable values('U2','1000,1001,1003,1004');
insert into UserTable values('U3','1000,1001,1003,1005,1006');
insert into UserTable values('U4','9');
commit;

SQL

--■■■Like演算子で部分一致を調べる方法1■■■
select b.UserID,a.GroupID
  from GroupTable a,UserTable b
 where b.SelectKey Like
'%' || replace(replace(replace(RTrim(a.SelectCode),'\','\\'),'%','\%'),'_','\_') || '%'
                  escape '\'
order by b.UserID,a.GroupID;

--■■■Like演算子で部分一致を調べる方法2(10g以降)■■■
select b.UserID,a.GroupID
  from GroupTable a,UserTable b
 where b.SelectKey Like
'%' || RegExp_Replace(replace(RTrim(a.SelectCode),'\','\\'),'([%_])','\\\1') || '%'
                  escape '\'
order by b.UserID,a.GroupID;

--■■■instr関数で部分一致を調べる方法■■■
select b.UserID,a.GroupID
  from GroupTable a,UserTable b
 where instr(b.SelectKey,RTrim(a.SelectCode)) > 0
order by b.UserID,a.GroupID;

解説

部分一致のチェックは、Like演算子でもInstr関数でもできます。

Like演算子を使う場合は、
%や_を含む可能性がある場合に、エスケープする必要があります。

---