読者です 読者をやめる 読者になる 読者になる

IPアドレスを0埋め/0埋め除去してSELECTする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