IPアドレスを0埋め/0埋め除去してSELECTするSQL

PostgreSQL SQL

IPアドレスが登録されているテーブルにSELECTしても上手くソートできない。

IPアドレスを0埋め（0詰め）しながらSELECTすれば、上手くソートできるので、やってみる。

【環境】
PostgreSQL 8.3
テーブル：NIC
カラム　：ip_adr


以下のように、0埋めしながらSELECTする。

SELECT
  lpad(split_part(ip_adr, '.', 1), 3, '0') || '.' ||
  lpad(split_part(ip_adr, '.', 2), 3, '0') || '.' ||
  lpad(split_part(ip_adr, '.', 3), 3, '0') || '.' ||
  lpad(split_part(ip_adr, '.', 4), 3, '0') AS ip
FROM
  nic
ORDER BY
  ip;


各関数は以下の動作をする。

lpad(string, length, fill-text)
　→左側から指定文字で埋める

split_part(string, delimiter, field)
　→デリミタで区切った結果から指定したカラムを返す（.で区切り1～4番目を返す）



また、既に0埋めされているIPアドレスから、0埋めを除去しながらSELECTするには以下のようにする。

SELECT
  regexp_replace(split_part(ip_adr, '.', 1), '^0{1,2}', '') || '.' ||
  regexp_replace(split_part(ip_adr, '.', 2), '^0{1,2}', '') || '.' ||
  regexp_replace(split_part(ip_adr, '.', 3), '^0{1,2}', '') || '.' ||
  regexp_replace(split_part(ip_adr, '.', 4), '^0{1,2}', '') AS ip
FROM
  nic
ORDER BY
  ip;


regexp_replace(string, before, after)
　→POSIX正規表現パターンにマッチする部分文字列を新規テキストと置換する。
　　「^」で、ピリオドで区切った各フィールドの最左桁にマッチ。
　　{1,2}で000のフィールドが空白にならないようにする。


【2016/6/15 追記】
もっと簡単な方法があります。
shobon.hatenablog.com