postgresql の 数値キャストエラー には参った…

昔のDBがそのまま利用されながら新しいシステムを使っている場合…

1カラムを 数値型で宣言
2カラムを text 型で宣言

として、
1カラム目を区分
2カラム目を値

と利用しているDBがありました。

その結果、数値変換できるカラムとできないカラムが混在してます。
WHERE で 「1カラム=1 AND 2カラム::SMALLINT=1」 とかやると空白データ、nullデータ、文字データ等々エラーが続発…
ってわけでこれに落ち着きました。

 CAST(REGEXP_REPLACE('0' || COALESCE(2カラム, '0'), '[^0-9]+', '0', 'g') AS SMALLINT) = 103
 

１．COALESCE で null データを 0に変換
２．'0' || で 先頭に0を加える事で、 空文字に対応
３．REGEXP_REPLACE で 半角数値以外を 0に変換
４．CAST で 半角数値を SMALLINT に変換


これで全パターン行けただろう！？
さすが20年以上前のDB設計を使いまわしてるシステムだ…
く○だろ！


ちなみに、なぜこの記事を書いたかというと、全部対応してるものが見つけられなかったから…
あ、そういう意味だと、小数点データには対応して無いかもねー
正規表現変えてみて