Link to home
Start Free TrialLog in
Avatar of talahi
talahi

asked on

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.
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Why not:
AND inventory_nbr like 'ET-%'
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?
ASKER CERTIFIED SOLUTION
Avatar of johnsone
johnsone
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of talahi

ASKER

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.
Avatar of talahi

ASKER

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?
>>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

Personally I like:
select inventory_nbr from tab1 where inventory_nbr not like 'ET-%' or  inventory_nbr IS NULL;

Open in new window

Avatar of talahi

ASKER

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-%')
Avatar of talahi

ASKER

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