Solved

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

Posted on 2015-02-20
10
224 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 76

Expert Comment

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

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 34

Accepted Solution

by:
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-')
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 76

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 76

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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

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

Suggested Solutions

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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 video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

856 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