Skip to Main Content

Shorthand inequality operator to deal with Nulls

John_KJul 9 2018 — edited Sep 14 2018

Where nulls are present in data it's a bit tedious to deal with them with inequalities.

create table xxtest as

  select null a from dual union all

  select 'X' a from dual

select * from xxtest where a != 'X' or a is null;

select * from xxtest where nvl(a,'Some out of bounds value') != 'X';

select * from xxtest where sys_op_map_nonull(a) != sys_op_map_nonull('X');

Personally I prefer the first method as I don't like using dummy "out of bounds" values because in dynamic systems with binds etc, they can suddenly become in bounds, and definitely not keen on undocumented functions. However ideally, I'd like to be able to:

select * from xxtest where a !~ 'X'

or similar.

Post Details
Added on Jul 9 2018