Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 232
  • Last Modified:

Oracle Query testing for string using substr but also need to test for NULL?

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)
FROM table_name
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.
1 Solution
slightwv (䄆 Netminder) Commented:
Why not:
AND inventory_nbr like 'ET-%'
slightwv (䄆 Netminder) Commented:
Just reread this:
I'm testing for a column to see if it starts with character 'ET-'

What you have:  SUBSTR(trim(inventory_nbr), 1, 3) <> 'ET-') says Does NOT start with.

Which one do you want?

Depending on which way you might want 'is NOT null'?

Please show sample data and expected results.

For example given the data:

What you you want back?
johnsoneSenior Oracle DBACommented:
The answer as to why you need to add the NULL check is basically that in Oracle, NULL compared to anything is FALSE.  Always.  In fact, WHERE NULL = NULL is false.

Now, you could rewrite it as:

AND SUBSTR(trim(nvl(inventory_nbr, 'A')), 1, 3) <> 'ET-')
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

talahiAuthor Commented:
For the sample data I don't want 'ET-Hello' but I want everything else.  I don't want any record where the columns has a string that starts with 'ET'.  I want all other records.

BTW also tried nventory_nbr NOT like 'ET-%' (with and w/o the substr) and it didn't work.
talahiAuthor Commented:
AND SUBSTR(trim(nvl(inventory_nbr, 'A')), 1, 3) <> 'ET-') works so thanks for that (I'll post points later) but still don't understand why NULL has to be considered?
slightwv (䄆 Netminder) Commented:
>>but still don't understand why NULL has to be considered?

As johnsone stated:  Nothing can be compared to a NULL in Oracle.

>>For the sample data I don't want 'ET-Hello' but I want everything else

Then what you had should have worked:
AND (SUBSTR(trim(inventory_nbr), 1, 3) <> 'ET-' or  inventory_nbr IS NULL)

But you said it didn't work?

I did in my test:
create table tab1(inventory_nbr varchar2(10));

insert into tab1 values('ET-Hello');
insert into tab1 values('Hello');
insert into tab1 values(null);

select rownum,inventory_nbr from tab1 where SUBSTR(trim(inventory_nbr), 1, 3) <> 'ET-' or inventory_nbr is null;

Open in new window

slightwv (䄆 Netminder) Commented:
Personally I like:
select inventory_nbr from tab1 where inventory_nbr not like 'ET-%' or  inventory_nbr IS NULL;

Open in new window

talahiAuthor Commented:
Ok thanks.  After all these years I was not aware that NULL had to be considered separately when doing comparisons.
select inventory_nbr from tab1 where lnnvl(inventory_nbr like 'ET-%')
talahiAuthor Commented:
I discovered the reason for this function before just now learning it exists. Humbled again, thanks!

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now