Hi,
I would like to know how to use IN and NOT LIKE operator together in the query. (Oracle XE 11g) For example -
CREATE TABLE T1 (CODE VARCHAR2(10));
INSERT INTO T1 VALUES('01');
INSERT INTO T1 VALUES('01001');
INSERT INTO T1 VALUES('01002');
INSERT INTO T1 VALUES('02');
INSERT INTO T1 VALUES('02005');
INSERT INTO T1 VALUES('02008');
CREATE TABLE T2 (CODE VARCHAR2(10));
INSERT INTO T2 VALUES('02');
INSERT INTO T2 VALUES('05');
If I use
SELECT t1.*
FROM t1, t2
WHERE t1.code like t2.code||'%';
I get valid result i.e.
However, what I need is, NOT LIKE operator which should return only below values from T1 table (i.e. exclude everything from T1 table which matches with T2.CODE i.e. 02% and 05% -
01
01001
01002
I tried with below query which is, of course not correct one -
SELECT t1.*
FROM t1, t2
WHERE t1.code NOT like t2.code||'%';
Can someone help please. Thanks.
Regards,
-Anand