SQL Query with NOT LIKE IN
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';
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%'
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)
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%'