select * from
(select 'G01N 33/55' as ipc, 'G01N 34' as not_ipc),
(select 'G01N 33/55' as ipc, 'G01N 33' as not_ipc),
(select 'G01N 33/55' as ipc, string(null) as not_ipc)
where not ipc contains not_ipc or not_ipc is null
这将返回:
+-----+------------+---------+---+
| Row | ipc | not_ipc | |
+-----+------------+---------+---+
| 1 | G01N 33/55 | G01N 34 | |
| 2 | G01N 33/55 | null | |
+-----+------------+---------+---+
这是另一个:
select * from
(select 'G01N 33/55' as ipc, 'G01N 33' as not_ipc, 'C08K 3/08' as exclude, 'C08K 3/08,C08K 77/02' as other_classes),
(select 'G01N 33/55' as ipc, 'G01N 34' as not_ipc, string(null) as exclude, 'C08K 3/08,C08K 77/02' as other_classes),
(select 'G01N 33/55' as ipc, 'G01N 33' as not_ipc, string(null) as exclude, string(null) as other_classes),
(select 'G01N 33/55' as ipc, 'G01N 36' as not_ipc, string(null) as exclude, string(null) as other_classes),
(select 'G01N 33/55' as ipc, string(null) as not_ipc, 'C08K 3/08' as exclude, string(null) as other_classes)
where (not ipc contains not_ipc or not_ipc is null) and (not other_classes contains exclude or exclude is null or other_classes is null)
返回:
+-----+------------+---------+-----------+----------------------+---+
| Row | ipc | not_ipc | exclude | other_classes | |
+-----+------------+---------+-----------+----------------------+---+
| 1 | G01N 33/55 | G01N 34 | null | C08K 3/08,C08K 77/02 | |
| 2 | G01N 33/55 | G01N 36 | null | null | |
| 3 | G01N 33/55 | null | C08K 3/08 | null | |
+-----+------------+---------+-----------+----------------------+---+