CODEWITHSUNDEEP
sqlsql-server-2008t-sql
venkat
venkat

Reputation: 5738

SQL query with NOT LIKE IN

Please help me to write an SQL query with the condition as NOT LIKE IN.

SELECT *
FROM Table1
WHERE EmpPU NOT Like IN ('%CSE%', '%ECE%', '%EEE%');

The above example returns an error.

Upvotes: 82

Views: 537868

Answers (10)

Paddy
Paddy

Reputation: 33857

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

Upvotes: 144

Mark Schultheiss
Mark Schultheiss

Reputation: 34168

This is similar to others examples but uses a CTE and a SQL Server table value constructor. References:

This is similar to using UNION or the STRING_SPLIT examples but I find this syntax slightly cleaner when I am changing the match conditions list and the list gets quite long. You could also join your unlike v CTE to some other table for a more complex scenario.

;WITH unlike AS 
(
    SELECT v.notme
    FROM (VALUES
        ('%CSE%'),
        ('%ECE%'),
        ('%EEE%')
        ) AS v (notme) 
)
SELECT * 
FROM Table1 AS t
WHERE NOT EXISTS (SELECT * FROM unlike WHERE t.EmpPU LIKE unlike.notme)

Upvotes: 0

Adrian Bathurst
Adrian Bathurst

Reputation: 31

You can use SQL Server's STRING_SPLIT and use a mix of wildcards.

DECLARE @IgnoreWords NVARCHAR(MAX)= '%Word1%,%Word2,Word3%,Word4';

SELECT MyTable.MyCol
FROM
(SELECT 'xxWord1xx' AS MyCol) AS MyTable
WHERE NOT EXISTS
    (SELECT * FROM
        (SELECT value AS ToIgnore FROM STRING_SPLIT(@IgnoreWords, ',')) AS Words
    WHERE MyTable.MyCol LIKE Words.ToIgnore)

Upvotes: 0

Bruno Manuel Rosas Marques
Bruno Manuel Rosas Marques

Reputation: 51

Or use EXCEPT:

    SELECT * FROM Table1 
    EXCEPT
    SELECT * FROM Table1 
    WHERE EmpPU LIKE '%CSE%'  
       OR EmpPU LIKE '%ECE%' 
       OR EmpPU LIKE '%EEE%'

Upvotes: 0

Albatros
Albatros

Reputation: 1

Code is as below:

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

Upvotes: 0

shashankqv
shashankqv

Reputation: 525

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';

Upvotes: 21

Diego
Diego

Reputation: 36136

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

Upvotes: 6

Arion
Arion

Reputation: 31239

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
    )

Upvotes: 3

MatBailie
MatBailie

Reputation: 86706

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)

Upvotes: 29

Mursa Catalin
Mursa Catalin

Reputation: 1449

you can try this 

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

Upvotes: 1

Related Questions