SQL Query with NOT LIKE IN

Question

Please help me to write a sql query with the conditions as 'NOT LIKE IN'

Select * from Table1 where EmpPU NOT Like IN ('%CSE%', '%ECE%', '%EEE%') 

Getting error.

Answer 1

You cannot combine like and in. The statement below would do the job though:

Select * from Table1  where EmpPU NOT Like '%CSE%'  AND EmpPU NOT Like '%ECE%'  AND EmpPU NOT Like '%EEE%' 

Answer 2

That's because you're mixing two syntax together.

If you always have exactly those three values, you can just AND the results of three LIKE expressions.

SELECT   * FROM   Table1 WHERE       EmpPU NOT LIKE '%CSE%'   AND EmpPU NOT LIKE '%ECE%'   AND EmpPU NOT LIKE '%EEE%' 

If you need to do it for "any number" of values, you can put the values into a table and do a join.

WITH   myData AS (             SELECT '%CSE%' AS match   UNION ALL SELECT '%ECE%' AS match   UNION ALL SELECT '%EEE%' AS match )  SELECT   * FROM   Table1 LEFT JOIN   myData     ON Table1.EmpPU LIKE myData.match WHERE   myData.match IS NULL 

OR...

WITH   myData AS (             SELECT '%CSE%' AS match   UNION ALL SELECT '%ECE%' AS match   UNION ALL SELECT '%EEE%' AS match )  SELECT   * FROM   Table1 WHERE   NOT EXISTS (SELECT * FROM myData WHERE Table1.EmpPU LIKE match)