在NOT IN子句中传递定界字符串 [英] Passing a delimited string in the NOT IN clause

问题描述

下面的SQL从概念上复制了我要解决的问题.尽管传递了NOT IN子句，但仍返回了所有三个记录.

The below SQL conceptually replicates the problem I'm trying to solve. Despite passing a NOT IN clause all three records are returned.

SELECT * FROM (
SELECT 'JACK' AS VALUE FROM DUAL
UNION
SELECT 'JOHN' AS VALUE FROM DUAL
UNION
SELECT 'BOB' AS VALUE FROM DUAL
) WHERE VALUE NOT IN (SELECT 'BOB,JOHN' FROM DUAL);

我有一个表，其中包含一个定界字符串，我希望将其用作从数据集中排除记录的条件.但是，我遇到的问题是返回的字符串没有分解成分隔的项目.我希望以上内容翻译为:

I have a table that holds a delimited string that I want to use as the criteria to exclude records from the dataset. However, the problem I have is that the returned string is not broken down into its delimited items. I want the above to translate to:

SELECT * FROM (
SELECT 'JACK' AS VALUE FROM DUAL
UNION
SELECT 'JOHN' AS VALUE FROM DUAL
UNION
SELECT 'BOB' AS VALUE FROM DUAL
) WHERE VALUE NOT IN ('BOB','JOHN');

推荐答案

您可以使用regexp_substr来解决该问题:

You can use regexp_substr for that problem:

SELECT * FROM (
SELECT 'JACK' AS VALUE FROM DUAL
UNION
SELECT 'JOHN' AS VALUE FROM DUAL
UNION
SELECT 'BOB' AS VALUE FROM DUAL
)
WHERE VALUE NOT IN (SELECT regexp_substr('BOB,JOHN','[^,]+', 1, LEVEL) FROM dual CONNECT BY regexp_substr('BOB,JOHN', '[^,]+', 1, LEVEL) IS NOT NULL)

这篇关于在NOT IN子句中传递定界字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！