NULLIF function

By | June 1, 2011

I just learned about NULLIF not too long ago, and again today found a good use case for it. I took me a while to find the function again though, so I figure if I put it here it will be easier to find next time.

Basically the function NULLIF compares 2 values. If they are equal, it returns null, otherwise it returns the first value.

In the example below, new_val1 and new_val2 are equivalent:

with data as
    select rownum rn
    from dual
    connect by level <= 5
    nullif(rn,3) new_val1,
    case when rn = 3 then null else rn end new_val2
from data;
        RN   NEW_VAL1   NEW_VAL2
---------- ---------- ----------
         1          1          1
         2          2          2
         4          4          4
         5          5          5

Leave a Reply

Your email address will not be published. Required fields are marked *

Turn on pictures to see the captcha *