OracleのSQLメモ(その1)

• VARCHAR2は動的にサイズを確保してくれると思っていた．

• 10桁未満の文字をVARCHAR2(10)にキャストすれば8桁になると勝手に思っていた．

• キャストした後別のテーブルのVARCHAR2(10)のカラムと比較していたが巧くいかなかった．

• 以下のようなSQLを実行しても結果が0件だった．

  • MASTER側のカラムUSER_IDは8桁(定義はVARCHAR2(10))、CUSTOM_USERINFOのカラムCUSTOM_USER_IDは10桁になっているが5,6桁目を除けば一致するという仕様になっている．

SELECT * FROM MASTER
  WHERE MASTER.USER_ID IN (
    SELECT CAST(SUBSTR(CUSTOM_USER_ID,1,4)||SUBSTR(CUSTOM_USER_ID,7,10) AS VARCHAR2(10)) FROM CUSTOM_USERINFO
  );

• もしかしたらVARCHAR2(10)にキャストした時は10文字未満でも10文字になるよう埋められるのではないかと思い実験．

• VARCHAR2(10)にキャストする．

SELECT '__'||CAST(SUBSTR(CUSTOM_USER_ID,1,4)||SUBSTR(CUSTOM_USER_ID,7,10) AS VARCHAR2(10))||'__' FROM CUSTOM_USERINFO

• 結果
  • 丁寧に10桁固定になるようで、後ろに2byteの余白あり．

__08299025  __
__08299045  __

• VARCHAR2(8)にキャストする．

SELECT '__'||CAST(SUBSTR(CUSTOM_USER_ID,1,4)||SUBSTR(CUSTOM_USER_ID,7,10) AS VARCHAR2(8))||'__' FROM CUSTOM_USERINFO

• 結果
  • 下記のとおり、予想通り．

__08299025__
__08299045__

結論

• ORACLEのSQLで文字列(VARCHAR2)にCASTするときは文字列長も意識しないと比較に失敗する．

補足

• 以下が気になる．
  • NUMBER型はどうなるのか
  • CASTするとき動的にサイズを変更できるのか？(余計な空白で埋められるのを防げるのか)