Postgresで正規表現を使った置換

sql正規表現って使ったことありませんでしたが、
たまたま仕事で必要になったので使ってみました。

今回の要件

要は
とある列の前後の半角/全角スペースを削除する
ということがしたかったのです。
正規表現はあまり得意ではないのですが
この程度なら問題なし!

実演(準備)

では実演してみます*1
適当なテーブルを作ってみましょう。

drop table tsubasa;
create table tsubasa (name text, killershot text);
insert into tsubasa values ('大空','ドライブシュート ');
insert into tsubasa values ('日向',' タイガー ショット');
insert into tsubasa values ('立花','スカイラブハリケーン');
insert into tsubasa values ('平松','   カミソリ シュート');
insert into tsubasa values ('新田','     隼シュート      ');

killershot列に、適当に全角スペースと半角スペースをばらまいています。


実演(確認)

いきなり更新するのではなく、
更新前、更新後でどのように変換されるのかを確認してみましょう。
今回の正規表現は、
where文で検索絞り込みするための「~」と、
正規表現で置換を行う「regexp_replace」を
利用しています。

前スペースの削除
select
    '('||killershot||')' as origin,  -- オリジナルの値,
    '('||regexp_replace(killershot,'^( | )+','') ||')' as replace -- 置換後の値
from tsubasa
where killershot ~ '^( | )+'  -- 正規表現で検索
;
          origin           |       replace        
---------------------------+----------------------
 ( タイガー ショット)      | (タイガー ショット)
 (   カミソリ シュート) | (カミソリ シュート)
 (     隼シュート      ) | (隼シュート      )
(3 rows)

左が変換前、右が変換後を想定したレコードです。
うまい具合に前スペースが削除されているのがわかります。
そして置換対象レコードも前スペースが存在するもののみにしてます。

後ろスペースの削除
select
    '('||killershot||')' as origin,  -- オリジナルの値,
    '('||regexp_replace(killershot,'( | )+$','') ||')' as replace -- 置換後の値
from tsubasa
where killershot ~ '( | )+$'  -- 正規表現で検索
;
          origin           |      replace       
---------------------------+--------------------
 (ドライブシュート )       | (ドライブシュート)
 (     隼シュート      ) | (     隼シュート)

後ろスペースも問題ないですね。*2

実演(更新)

では、実際に更新してみます。

前スペースの削除
update tsubasa
  set
     killershot = regexp_replace(killershot,'^( | )+','')
where killershot ~ '^( | )+
;
後ろスペースの削除
update tsubasa
  set
     killershot = regexp_replace(killershot,'( | )+$','')
where killershot ~ '( | )+$'
;
再確認
select
   '('||killershot||')' as origin,  -- オリジナルの値,$
   '('||regexp_replace(killershot,'^( | )+','') ||')' as replace -- 置換後の値$
from tsubasa
;
         origin         |        replace         
------------------------+------------------------
 (スカイラブハリケーン) | (スカイラブハリケーン)
 (タイガー ショット)    | (タイガー ショット)
 (カミソリ シュート)   | (カミソリ シュート)
 (ドライブシュート)     | (ドライブシュート)
 (隼シュート)           | (隼シュート)


おk。想定通りの動きになってますね。
sql正規表現って、割と便利かもしれませんのでどしどし使ってみてください!*3

※公式ドキュメントはこちら
http://www.postgresql.jp/document/pg837doc/html/functions-matching.html#FUNCTIONS-POSIX-REGEXP

*1:今回はpostgres8.4.1で実行しています

*2:前後を同時変換しようとしたらうまくいかなかった…何故だ

*3:パフォーマンスがどうなのか気になる。暇ができたらまたベンチとってみるか