10-152 正規表現で、IPアドレスの左に0埋め

SQLパズル

IPTable
IP
-------------
1.1.1.1
10.10.10.10
83.133.96.2
83.133.96.3
83.133.96.10
83.133.96.20
83.133.97.2
83.133.97.3
83.133.97.10
83.133.98.2
83.133.98.3
83.133.98.10
192.2.0.0
192.168.0.1
192.168.0.2
192.168.0.10
192.168.0.20
192.168.1.2
192.168.1.10
255.255.255.0

IPアドレスを、
3桁になるように、左に0を埋める。

出力結果
beforeIP       afterIP
-------------  ---------------
1.1.1.1        001.001.001.001
10.10.10.10    010.010.010.010
83.133.96.2    083.133.096.002
83.133.96.3    083.133.096.003
83.133.96.10   083.133.096.010
83.133.96.20   083.133.096.020
83.133.97.2    083.133.097.002
83.133.97.3    083.133.097.003
83.133.97.10   083.133.097.010
83.133.98.2    083.133.098.002
83.133.98.3    083.133.098.003
83.133.98.10   083.133.098.010
192.2.0.0      192.002.000.000
192.168.0.1    192.168.000.001
192.168.0.2    192.168.000.002
192.168.0.10   192.168.000.010
192.168.0.20   192.168.000.020
192.168.1.2    192.168.001.002
192.168.1.10   192.168.001.010
255.255.255.0  255.255.255.000

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

データ作成スクリプト

create table IPTable(Val) as
select '1.1.1.1'       from dual union
select '10.10.10.10'   from dual union
select '192.2.0.0'     from dual union
select '192.168.0.1'   from dual union
select '192.168.0.2'   from dual union
select '192.168.0.10'  from dual union
select '192.168.0.20'  from dual union
select '192.168.1.2'   from dual union
select '192.168.1.10'  from dual union
select '255.255.255.0' from dual union
select '83.133.96.2'   from dual union
select '83.133.96.3'   from dual union
select '83.133.96.10'  from dual union
select '83.133.98.10'  from dual union
select '83.133.97.2'   from dual union
select '83.133.97.3'   from dual union
select '83.133.97.10'  from dual union
select '83.133.98.2'   from dual union
select '83.133.98.3'   from dual union
select '83.133.96.20'  from dual;

SQL

col afterIP for a15

--■■■正規表現を使う方法1(10g以降)■■■
select Val as beforeIP,
RegExp_Replace(RegExp_Replace(Val,'(^|\.)','\1000')
              ,'(^|\.)[0-9]*([0-9]{3})'
              ,'\1\2') as afterIP
from IPTable
order by afterIP;

--■■■正規表現を使う方法2(10g以降)■■■
select Val as beforeIP,
RegExp_Replace(RegExp_Replace(Val,'([0-9]+)','000\1'),
               '0*([0-9]{3})','\1') as afterIP
from IPTable
order by afterIP;

--■■■instr関数を使う方法■■■
select Val as beforeIP,
to_char(substr(Val,1,instr(Val,'.')-1),'fm000') || '.' ||
to_char(substr(Val,instr(Val,'.')+1    ,instr(Val,'.',1,2)-instr(Val,'.')-1),'fm000') || '.' ||
to_char(substr(Val,instr(Val,'.',1,2)+1,instr(Val,'.',1,3)-instr(Val,'.',1,2)-1),'fm000') || '.' ||
to_char(substr(Val,instr(Val,'.',1,3)+1),'fm000') as afterIP
  from IPTable
order by afterIP;

--■■■model句を使う方法(10g以降)■■■
select beforeIP,afterIP
  from IPTABLE
 model
 dimension by (RowNum as soeji)
 measures(Val as beforeIP,cast(Val as varchar2(15)) as afterIP)
 rules ITERATE (100) (afterIP[any] = RegExp_Replace(afterIP[CV()],'(^|\.)([0-9]{1,2})($|\.)','\10\2\3'))
order by beforeIP;

解説

正規表現を使う方法では、
format関数のないasp(VBScript)でよく使われた、
古のロジックである
right("000" & 対象文字列,3)
の考え方を使ってます。

Oracle10gの正規表現は、
先読みも、戻り読みも、使えないので、
キャプチャして置換するといった方法を使う必要があります。

Oracle9iの場合は、ピリオドで分割して、
to_char関数を使えばいいでしょう。

---