What kind of field is this? The IN operator cannot be used with a single field, but is meant to be used in subqueries or with predefined lists:

-- subquery
SELECT a FROM x WHERE x.b NOT IN (SELECT b FROM y);
-- predefined list
SELECT a FROM x WHERE x.b NOT IN (1, 2, 3, 6);

If you are searching a string, go for the LIKE operator (but this will be slow):

-- Finds all rows where a does not contain "text"
SELECT * FROM x WHERE x.a NOT LIKE '%text%';

If you restrict it so that the string you are searching for has to start with the given string, it can use indices (if there is an index on that field) and be reasonably fast:

-- Finds all rows where a does not start with "text"
SELECT * FROM x WHERE x.a NOT LIKE 'text%';

Yes, you should be able to use NOT on any boolean expression, as mentioned in the SQL Server Docs here. And, it would look something like this:

SELECT *
  FROM table
 WHERE NOT CONTAINS (column, ‘searchword’)

To search for records that do not contain the 'searchword' in the column. And, according to

Performance of like '%Query%' vs full text search CONTAINS query

this method should be faster than using LIKE with wildcards.

You can use the SUBSTRING function in your SQL query to extract the third and last letter of the string field and then use the NOT LIKE operator to exclude records that have those specific characters. Here's an example of a query that excludes records where the third and last letter of the string_field are 'A':

SELECT *
FROM [data_extension]
WHERE 
    (SUBSTRING(string_field, 3, 1) NOT LIKE 'A')
    AND (SUBSTRING(string_field, LEN(string_field), 1) NOT LIKE 'A')

You can replace the 'A' with any character you want.

WHERE NOT (someColumn LIKE '%Apples%')