CODEWITHSUNDEEP
sqlsql-server
Robert E. McIntosh
Robert E. McIntosh

Reputation: 6135

Exclude from result if its column contains any value in list

Lets say I have a SQL table that has the id's 1 through 30 but I want to exclude the numbers 7,10,21 from the sql.

How would I do this without writing something like

SELECT * 
FROM TBL 
WHERE COL <> '7' AND COL <> '10' AND COL <> '21'

But instead, write something like

SELECT * 
FROM TBL 
WHERE COL NOT IN (@IDS)

When trying the example, it works if @IDS is equal to one number, but if it is equal to multiple numbers all records show.

To clarify the reason it has to be a list of numbers is that is how it is passed to the SQL. I am unable to change how the data is passed.

To clarify more because I should have stated it in the original question. I do not know the exact numbers being passed into the SQL Statement, the ones I provided were for the example, but it could be any number.

Upvotes: 2

Views: 1508

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270091

You can write this as:

WHERE COL NOT IN (7, 10, 21)

If you want to pass in a string, then you can split it. In SQL Server 2016+, you an use string_split() (and you can get SPLIT() functions for earlier versions on the web).

WHERE COL NOT IN (SELECT * FROM STRING_SPLIT('7,10,21'))

Do note that this does conversion from string to integers, which might prevent the use of indexes. So:

WHERE COL NOT IN (SELECT CONVERT(INT, value) FROM STRING_SPLIT('7,10,21'))

Or NOT EXISTS:

WHERE EXISTS (SELECT 
              FROM STRING_SPLIT('7,10,21')
              WHERE ?.COL = TRY_CONVERT(INT, value) 
             )

Upvotes: 3

Related Questions