CODEWITHSUNDEEP
oracle-databaseplsql
Sulphy
Sulphy

Reputation: 776

Passing a delimited string in the NOT IN clause

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');

Upvotes: 1

Views: 1205

Answers (2)

MT0
MT0

Reputation: 167981

'BOB,JOHN' is not a list of two string values it is one string value that just happens to contain a comma in the string and:

'JACK' = 'BOB,JOHN'
'JOHN' = 'BOB,JOHN'
'BOB'  = 'BOB,JOHN'

Are all false so your query will return all rows as matched by the NOT IN filter.

You can surround your values and list with the delimiter characters and test whether the value is not a sub-string of the list like this:

SELECT *
FROM (
  SELECT 'JACK' AS VALUE FROM DUAL UNION ALL
  SELECT 'JOHN' AS VALUE FROM DUAL UNION ALL
  SELECT 'BOB'  AS VALUE FROM DUAL
)
WHERE INSTR( ',' || 'BOB,JOHN' || ',', ',' || value || ',' ) = 0

Or you can use a user-defined collection:

CREATE OR REPLACE TYPE stringlist IS TABLE OF VARCHAR2(20);

Then use the MEMBER OF operator to test whether a value is a member of the collection:

SELECT *
FROM (
  SELECT 'JACK' AS VALUE FROM DUAL UNION ALL
  SELECT 'JOHN' AS VALUE FROM DUAL UNION ALL
  SELECT 'BOB'  AS VALUE FROM DUAL
)
WHERE  VALUE NOT MEMBER OF StringList( 'BOB', 'JOHN' );

Upvotes: 1

gobnepla
gobnepla

Reputation: 644

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)

Upvotes: 1

Related Questions