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 |