misc.log

日常茶飯事とお仕事と

Oracle、to_charで'00000000'に変換したあとのsubstrで桁がおかしい?

Oracleのシステムで、「20160825」のようにnumber型で年月日情報持っているというテーブルがあり、これを別RDBに移植するという作業をやってます。

で、このデータを扱う処理では下記のようなto_char処理で年月日を「2016/08/25」に変換していました(下記サンプルは便宜上 from dualにしています)。

select substr(TO_CHAR('20160825','00000000'),2,4) ||
  '/' ||substr(to_Char('20160825','00000000'),6,2)|| 
  '/' ||substr(to_Char('20160825','00000000'),8,2) from dual


これ、よく見ると「2016…」の「2文字目から始まる4文字」を切り出して「2016」にしてるんですね。でも、予想ではto_charで8ケタの数字文字列に変換したら、年号は1文字目から4文字のはず。

なんでだろう?と調べたら、フォーマット文字列「00000000」の場合、先頭1バイトは正負の符号になるようで。というわけで、上記のSQLだとsubstrに掛ける前の年月日は「+20160825」になっており、結果として年号は2文字目からになっているようです。

これを回避するには?

to_charの書式先頭に「FM」を付ければ、空白や符号を除去して文字列置換してくれるようです。

select substr(TO_CHAR('20160825','FM00000000'),1,4) || 
  '/' ||substr(to_Char('20160825','FM00000000'),5,2)||
  '/' ||substr(to_Char('20160825','FM00000000'),7,2) from dual

こんな感じ。しらなかった。
ってか、移植前のコードを書いた人も知らなかったみたい。

基礎から学ぶOracle SQLチューニング (DB Magazine SELECTION)

基礎から学ぶOracle SQLチューニング (DB Magazine SELECTION)