Oracle Query testing for string using substr but also need to test for NULL?
Posted on 2015-02-20
In the WHERE clause, I'm testing for a column to see if it starts with character 'ET-'. If it has those characters I don't want to retrieve those records. So my WHERE clause states,
AND SUBSTR(trim(inventory_nbr), 1, 3) <> 'ET-')
I noticed if this inventory_nbr column was NULL, then the record was not retrieved so I added this check for NULL,
AND (SUBSTR(trim(inventory_nbr), 1, 3) <> 'ET-' or inventory_nbr IS NULL)
and the record was retrieved.
I also ran this query to check for NULL to prove this column was NULL,
SELECT inventory_nbr, DUMP(inventory_nbr, 1016)
where seqkey = 'xyz';
and DUMP(inventory_nbr, 1016) returned NULL
I don't understand the logic where the record would not be returned with just the substr test, without adding the NULL test also.