10-205 正規表現で0以上255以下の整数かを調べる

SQLパズル

ColorTable
ID  color
--  ------------
 1  R2G2B2
 2  R22G22B22
 3  R222G222B222
 4  R300G2B2
 5  R300G256B2
 6  R300G256B256

RGBカラーの
RとGとBをそれぞれ抽出する(それぞれ0以上255以下の場合のみ抽出)
全て抽出できたら、連結してtotalとして出力する。

出力結果
ID  color         red   green  blue  total
--  ------------  ----  -----  ----  ------------
 1  R2G2B2        R2    G2     B2    R2G2B2
 2  R22G22B22     R22   G22    B22   R22G22B22
 3  R222G222B222  R222  G222   B222  R222G222B222
 4  R300G2B2      null  G2     B2    null
 5  R300G256B2    null  null   B2    null
 6  R300G256B256  null  null   null  null

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

データ作成スクリプト

create table ColorTable(
ID    number(1),
color varchar2(12));

insert into ColorTable values(1,'R2G2B2');
insert into ColorTable values(2,'R22G22B22');
insert into ColorTable values(3,'R222G222B222');
insert into ColorTable values(4,'R300G2B2');
insert into ColorTable values(5,'R300G256B2');
insert into ColorTable values(6,'R300G256B256');
commit;

SQL

col red   for a8
col green for a8
col blue  for a8
col total for a20

--■■■正規表現の検索結果を使う方法■■■
select ID,color,
RTrim(red,'G') as red,
RTrim(green,'B') as green,
blue,
case when greatest(red,green,blue) is not null
     then RTrim(red,'G') || RTrim(green,'B') || blue end as total
from (select ID,color,
      RegExp_Substr(color,'R([0-9]{1,2}|[0-1][0-9]{2}|2[0-4][0-9]|25[0-5])G') as red,
      RegExp_Substr(color,'G([0-9]{1,2}|[0-1][0-9]{2}|2[0-4][0-9]|25[0-5])B') as green,
      RegExp_Substr(color,'B([0-9]{1,2}|[0-1][0-9]{2}|2[0-4][0-9]|25[0-5])$') as blue
        from ColorTable);

--■■■正規表現の検索結果を使わない方法■■■
select ID,color,
RTrim(red,'G') as red,
RTrim(green,'B') as green,
blue,total
from (select ID,color,
      RegExp_Substr(color,'R([0-9]{1,2}|[0-1][0-9]{2}|2[0-4][0-9]|25[0-5])G') as red,
      RegExp_Substr(color,'G([0-9]{1,2}|[0-1][0-9]{2}|2[0-4][0-9]|25[0-5])B') as green,
      RegExp_Substr(color,'B([0-9]{1,2}|[0-1][0-9]{2}|2[0-4][0-9]|25[0-5])$') as blue,
      RegExp_Substr(color,'R([0-9]{1,2}|[0-1][0-9]{2}|2[0-4][0-9]|25[0-5])'
                       || 'G([0-9]{1,2}|[0-1][0-9]{2}|2[0-4][0-9]|25[0-5])'
                       || 'B([0-9]{1,2}|[0-1][0-9]{2}|2[0-4][0-9]|25[0-5])$') as total
        from ColorTable);

解説

正規表現の検索結果を使う方法が分かりやすいと思います。

正規表現パズル 3-6 IP(0以上255以下の整数)を検索

---