Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE name (id int, type varchar(64));
INSERT ALL 
 into name values (1,'1ABC')
 into name values (2,'2DEF')
 into name values (3,'3DEF')
 into name values (4,'4IJK')
SELECT 1 FROM dual;
4 rows affected

CREATE TABLE match (type varchar(128), data varchar(128));
INSERT ALL
 into match values ('NOT %ABC% AND NOT %DEF%','NOT ABC AND NOT DEF')
 into match values ('%DEF%','DEF ONLY')
 into match values ('NOT %DEF% AND NOT %IJK%','NOT DEF AND NOT IJK')
SELECT 1 FROM dual;
3 rows affected

WITH criteria AS
(
 SELECT 
  data,
  regexp_count(m.type, ' AND ')  + 1 num,
  CASE WHEN REGEXP_SUBSTR(m.type,'(.*?)( AND |$)',1,levels.column_value) like 'NOT %' THEN 1 ELSE 0 END negate,
  replace(replace(REGEXP_SUBSTR(m.type,'(.*?)( AND |$)',1,levels.column_value), 'NOT '), ' AND ') match
 FROM match m INNER JOIN
  table(cast(multiset(select level from dual connect by level <= regexp_count(m.type, ' AND ')  + 1) as sys.OdciNumberList)) levels
   ON 1=1
)
SELECT name.id, name.type, criteria.data
FROM name INNER JOIN criteria
  ON
   (criteria.negate = 0 AND name.type LIKE criteria.match)
   OR
   (criteria.negate = 1 AND name.type NOT LIKE criteria.match)
GROUP BY name.id, name.type, criteria.data
HAVING COUNT(*) = MAX(criteria.num)
ORDER BY name.id


ID
TYPE
DATA


1
1ABC
NOT DEF AND NOT IJK

2
2DEF
DEF ONLY

3
3DEF
DEF ONLY

4
4IJK
NOT ABC AND NOT DEF

ID	TYPE	DATA
1	1ABC	NOT DEF AND NOT IJK
2	2DEF	DEF ONLY
3	3DEF	DEF ONLY
4	4IJK	NOT ABC AND NOT DEF