ORACLEの空文字の扱いにハマった話。

とあるアプリの構築時に出してしまったバグ。

• 以下のSQL/SELECTで、メアドの有無が正しく拾えない。

(
  CASE
    WHEN  e_mail_address    IS NOT NULL
          AND
          e_mail_address    != ''
    THEN  1
    ELSE  0
  END
)                       AS has_e_mail_address

調べてみたら、こんな記事が見つかった。

• [http://blog.livedoor.jp/i_am_best/archives/7870002.html](Oracle では文字長 0 の文字型カラム値は勝手に NULL になってしまう!?)
  • どうやらORACLEは、「''」をNULLとして扱うらしい。

検証してみる。

SELECT
     (CASE WHEN TRIM(' ')            = ''    THEN 1 ELSE 0 END)  AS TRIM_SPACE_EQUAL_EMPTY,          -- 0
     (CASE WHEN TRIM(' ')            = NULL  THEN 1 ELSE 0 END)  AS TRIM_SPACE_EQUAL_NULL,           -- 0
     (CASE WHEN TRIM(' ')            IS NULL THEN 1 ELSE 0 END)  AS TRIM_SPACE_IS_NULL,              -- 1
 --  (CASE WHEN TRIM(' ')            IS ''   THEN 1 ELSE 0 END)  AS TRIM_SPACE_IS_EMPTY,             -- ERROR
     (CASE WHEN REPLACE('A','A','')  = ''    THEN 1 ELSE 0 END)  AS REPLACE_TO_EMPTY_EQUAL_NULL,     -- 0
     (CASE WHEN REPLACE('A','A','')  IS NULL THEN 1 ELSE 0 END)  AS REPLACE_CHAR_TO_EMPTY_IS_NULL,   -- 1
 --  (CASE WHEN REPLACE('A','A','')  IS ''   THEN 1 ELSE 0 END)  AS REPLACE_CHAR_TO_EMPTY_IS_EMPTY,  -- ERROR
     NULL                                                        AS dummy
 FROM
     DUAL

• ヒントを元に考えると、この書き方が正しい様子。テスト結果もOKになった。

NVL2(TRIM(e_mail_address), 1, 0)

雑感

• こんなん、他のDB触ってた人は絶対間違えるがな…。