Advertisement
Advertisement


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.

2012/02/22
1
46
2/22/2012 10:55:33 AM

Accepted Answer

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%'
2016/02/12
99
2/12/2016 4:10:23 PM

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)
2012/02/22

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';
2015/02/18

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%'
2012/02/22

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
    )
2012/02/22

you can try this

Select * from Table1 where 
    EmpPU NOT Like '%CSE%'  
AND EmpPU NOT Like '%ECE%' 
AND EmpPU NOT Like '%EEE%'
2012/02/22

Source: https://stackoverflow.com/questions/9393439
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]