Exclude from result if its column contains any value in list

Question

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.

Seems like a duplicate [Parameterize an SQL IN clause](https://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause/) — Lukasz Szozda, May 01 '19 at 11:56
I would assume that `Col` is an `int`, so is `@IDS` a table variable? — Thom A, May 01 '19 at 11:56
`@IDS` is a string value, so is `Col` Sorry for not clarifying — Robert E. McIntosh, May 01 '19 at 12:00

Gordon Linoff · Accepted Answer · 2019-05-01T12:02:42.257

3

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

edited May 01 '19 at 12:02

answered May 01 '19 at 11:56

Gordon Linoff

1,242,037
58
646
786

2

I would not use `TRY_CONVERT`, when some value will be converted to `NULL` then entire query will return 0 rows. – Lukasz Szozda May 01 '19 at 11:58
2

@LukaszSzozda has a point there. If you change the `IN` to an `EXISTS` you could continue to use `TRY_CONVERT` and not have the behaviour though. That would then avoid an error if you can't convert to an `int` too. – Thom A May 01 '19 at 12:02
Thank you so much, I spent two days trying to do this and you solved it in seconds... I didn't even know `SPLIT_STRING` was a thing. – Robert E. McIntosh May 01 '19 at 12:19

Exclude from result if its column contains any value in list

1 Answers1