Improve company productivity with a Business Account.Sign Up

x
?
Solved

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

Posted on 2015-02-20
10
Medium Priority
?
234 Views
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.
0
Comment
Question by:talahi
10 Comments
 
LVL 79

Expert Comment

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

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:
ET-Hello
Hello
<null>

What you you want back?
0
 
LVL 36

Accepted Solution

by:
johnsone earned 2000 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-')
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:talahi
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.
0
 

Author Comment

by:talahi
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?
0
 
LVL 79

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);
commit;


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

Open in new window

0
 
LVL 79

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

0
 

Author Closing Comment

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

Expert Comment

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

Author Comment

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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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.

606 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