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

Posted on 2015-02-20
Last Modified: 2015-02-23
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.
Question by:talahi
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40621882
Why not:
AND inventory_nbr like 'ET-%'
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40621933
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?
LVL 35

Accepted Solution

johnsone earned 500 total points
ID: 40621942
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-')
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!


Author Comment

ID: 40621948
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.

Author Comment

ID: 40621982
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?
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40622005
>>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

LVL 77

Expert Comment

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

Open in new window


Author Closing Comment

ID: 40622144
Ok thanks.  After all these years I was not aware that NULL had to be considered separately when doing comparisons.
LVL 74

Expert Comment

ID: 40622145
select inventory_nbr from tab1 where lnnvl(inventory_nbr like 'ET-%')

Author Comment

ID: 40622168
I discovered the reason for this function before just now learning it exists. Humbled again, thanks!

Featured Post

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question