アナログCPU:5108843109

ゲームと音楽とプログラミング(酒と女とロックンロールのノリで)

('ω') < 転職してフロントエンド勉強中 あとFE聖戦1周クリアしてトラキアやりながらSQX待ち

Postgresqlの数値文字列変換

まだうまく飲み込めてないのでなんともですが、
PostgresqlMySQLに比べると暗黙の型変換をしてくれるところが少ないような気が。
その割に型変換するのに若干クセがあって忘れそうなので、いろいろ試してみたのをメモ。

数値→文字列

たぶん一番よく使うやつ。
TO_CHAR関数に、数値とフォーマットを与え、フォーマットどおりの文字列にしてくれます。

SELECT  
  TO_CHAR(1234, '99999')   -- '  1234'(ゼロ埋めしない+左に謎のスペース)
 ,TO_CHAR(1234, 'FM99999') -- '1234'(ゼロ埋めしない)
 ,TO_CHAR(1234, '9')       -- ' #'(桁が足りないとこうなる+左に謎のスペース)
 ,TO_CHAR(1234, 'FM9')     -- '#'(桁が足りないとこうなる)
 ,TO_CHAR(1234, '00000')   -- ' 01234'(左側をゼロ埋め+左に謎のスペース)
 ,TO_CHAR(1234, 'FM00000') -- '01234'(左側をゼロ埋め)

2番目の「FM999...」のパターンを一番よく使うかなと思うのですが、充分な桁数が必要というのが厄介。
仕様上最大になる桁数で書いておけばいいんですが、桁が大きくなる場合はあんまりスマートじゃない気が…

続いて負数と小数のいろいろ。

SELECT  
  TO_CHAR( 1234.5, '99999')       -- '  1235'
 ,TO_CHAR(-1234.5, '99999')       -- ' -1235'
 ,TO_CHAR(-1234.5, 'FM99999.99')  -- '-1234.5'
 ,TO_CHAR(-1234.5, 'FM99999.00')  -- '-1234.50'

FMを付けない場合、謎の左側スペースのうち
ひとつは符号用、残りは単にゼロで埋める代わりにスペースが入ってるだけ…みたいですね。
そして整数化するときは四捨五入されています。
小数として扱う場合は一番下のパターンをよく使うことになるかな。

その他のフォーマットも可能です。
クエリ内で必要にならない限りは使わないと思うけど。
(「見た目に関わる内容はsmartyやjsでやるべき(どうしてもという場合はPHP)」派)

SELECT  
  TO_CHAR(1234567, 'FM999,999,999') -- '1,234,567'(カンマ区切り)
 ,TO_CHAR(    123, '[9999]')        -- '[  123]'(何かで囲んでみる)
 ,TO_CHAR(    123, '[FM9999]')      -- '[123]'(↑だと案の定スペース入ったのでFM付ける)
 ,TO_CHAR(    123, 'FM[9999]')      -- '[123]'(↑位置を変えてみても大丈夫だった)
 ,TO_CHAR(    123, '[9999]FM')      -- '[123]'(↑最後でもええんかい!)
 ,TO_CHAR(    123, '/9/99/99/')     -- '/ /  1/23/'(使いどころはわからないが動く)
 ,TO_CHAR(    123, 'FM/9/99/99/')   -- '//1/23/'(↑のスペース削除版)
 ,TO_CHAR(   -123, '/9/99/99/')     -- '/ / -1/23/'(負数だとこんな感じ)

文字列→数値

今度は数値への変換なので
TO_NUMBER関数に文字列とフォーマットを与え、
フォーマットどおりに読んだ結果を返してくれます。

SELECT
  TO_NUMBER('123456'  , '9999999')     -- '123456'(素直にそのまま)
 ,TO_NUMBER('123456'  , '9')           -- '1'(桁数が足りないと指定桁数分だけ左から読む)
 ,TO_NUMBER('12_34_56', '99_99_99')    -- '123456'(変な形式の場合はフォーマットでも同様に指定)
 ,TO_NUMBER('12_34_56', '999999')      -- '1234'(桁数が足りないと左から読み、数字だけ抽出する模様)
 ,TO_NUMBER('12_34_56', '999999999')   -- '123456'(桁数が十分あればいい感じに数字だけ抽出する模様)
 ,TO_NUMBER('12_34.56', '999999999')   -- '123456'(勝手に小数と認識してくれたりはしない)
 ,TO_NUMBER('12_34.56', '9999999.999') -- '1234.56'(小数として扱いたいときはドットを適当な位置へ)
 ,TO_NUMBER('-2_34.56', '9999999.999') -- '-234.56'(負数は認識してくれる)

参考
PostgreSQLで to_char()すると前に半角スペースが入る - かわろぐ
https://www.postgresql.jp/document/9.4/html/functions-formatting.html