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.

Solution 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%'

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

Solution 3

If you have set of words which you want to include/exclude in search from a particular column. You may want to use regular expression function of mysql.

Exclude set of words from a column :

SELECT
  *
FROM
  Table1
WHERE
      EmpPU NOT REGEXP 'CSE|ECE|EEE';

Search set of words from a column :

SELECT
  *
FROM
  Table1
WHERE
      EmpPU REGEXP 'CSE|ECE|EEE';

Solution 4

you cant combine LIKE and IN

you can do:

select * from Table1
where EmpPU not in ('%CSE%', '%ECE%', '%EEE%')

but you wont benefit from the % wildcard

if you need the % the only option is:

Select * from Table1
where EmpPU not like '%CSE%' and  EmpPU not like '%ECE%' and EmpPU not like '%EEE%'

Solution 5

Or you can do it like this:

SELECT 
    * 
FROM 
    Table1
WHERE NOT EXISTS
    (
        SELECT
            NULL
        FROM
        (
            SELECT '%CSE%' AS column1 UNION ALL 
            SELECT '%ECE%' UNION ALL 
            SELECT '%EEE%'
        ) AS tbl
        WHERE Table1.EmpPU LIKE tbl.column1
    )

View more solutions

Comments

[venkat]

I want in a one go of all parameters instead of write multiple NOT LIKE words

[venkat]

With your query getting ERROR as Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'Like'.

[MatBailie]

Except that you mean AND rather than OR? And you need to specify the field name in there too? [The last part of this answer is character for character identical to a deleted answer...]

[Diego]

no, its OR. Only one of the conditions must be true to exclude the record. A row with "EEE" in it must be excluded, but it is not like CSE

[MatBailie]

That is exactly the point. A row is included if the WHERE clause evaluates to true, to let's take 'SMEEEG' as an example and try your code... ('SMEEEG' NOT LIKE '%CSE%') OR ('SMEEEG' NOT LIKE '%ECE%') OR ('SMEEEG' NOT LIKE '%EEE%') => (TRUE) OR (TRUE) OR (FALSE) => TRUE. Your code includes 'SMEEEG' even though it is like '%EEE%'. You need NOT((x LIKE a) OR (x LIKE b) OR (x LIKE c)) or you need (x NOT LIKE a) AND (x NOT LIKE b) AND (x NOT LIKE c). Either way, what you have doesn't work. Sorry.

[Jabberwockey]

This is a better answer since it is more along the lines of NOT LIKE IN (...), making it easier to maintain the query. Performance-wise the regex might be more difficult.

[Mr Lister]

The OP is asking about T-SQL.