How to use the With NOT IN to strip out values from a

Question

I am trying to use the NOT IN function to return an output with certain fields stripped out.

For example I am trying return a table without the value 'Recruiting' or 'Natural' contained in the department field.

I tried the following:

SELECT
  *
FROM dbo.investmgmt
WHERE investmgmt.department NOT IN ('%Natural%')

I have created some sample data:

CREATE TABLE investmgmt (
    department varchar(250),
    company_name varchar(100))

INSERT investmgmt VALUES
('Strategy and Investment at ByteDance','ByteDance'),
('Investment management','Devon Equity Management Limited'),
('Senior Investment and Asset Manager','The Instant Group'),
('Director - Investment Banking & Law at Pinpoint Partners','Pinpoint Partners'),
('Recruiting talented Developers/Engineers into the Investment Banking and Investment Management Industries','Cornwallis Elt'),
('Investment Manager at Beringea','Beringea'),
('Investment Management - Natural Capital and Sustainable Farmland','Climate Asset Management'),
('Principal, Fund Manager','Columbus Capital Ltd'),
('CEO | ESG Investment Partners | Green Economy | ESG Procurement | Asset Management | M&A |','IZECORP INVESTMENTS'),
('Investment Banking Analyst at BNP Paribas','Deloitte'),
('Property Investment | Property Development | Finance','Nova Financial Group')

SELECT * FROM investmgmt

Any thoughts?

Answer 1

NOT IN is not a function. Let's start from beginning. If you say:

SELECT * FROM tbl WHERE col IN (1, 2, 3)

This is a syntactic shortcut for

SELECT * FROM tbl WHERE col = 1 OR col = 2 OR col 3

You can easily see this with help of an error message.

CREATE TABLE #t (a int NOT NULL)
SELECT a FROM #t WHERE b IN (1, 2, 3)

You get these errors:

Msg 207, Level 16, State 1, Line 26
Invalid column name 'b'.

Msg 207, Level 16, State 1, Line 26
Invalid column name 'b'.

Msg 207, Level 16, State 1, Line 26
Invalid column name 'b'.

You get there error messages, because before the binder comes around to look for the columns, the parser has already rewritten the IN to a list of OR conditions.

Now that you know what IN means, you also understand what NOT IN means. That is:

SELECT * FROM tbl WHERE col NOT IN (1, 2, 3)

is the same as

SELECT * FROM tbl WHERE NOT (col = 1 OR col = 2 OR col = 3)

Or, with some boolean algebra:

SELECT * FROM tbl WHERE col <> 1 AND col <> 2 AND col <> 3

In your example you had %Natural%. I assume that you had some wildcard thingie in mind. If you want to use wildcards, you need to use the LIKE operator. But for the LIKE operator there is no syntactic shortcut, but you need to write

SELECT * FROM tbl WHERE col NOT LIKE '%this%' AND col NOT LIKE '%that%'