While working with MySQL database i needed to write a query which will exclude rows where a column contains one or more words from a set of words. i.e I want to fetch data from student from table where name doesn’t contain words shashank,john etc.

Initially i was trying to write a query like this  :

select * from TABLE
where
Column NOT Like IN
('%VALUE1%', '%VALUE2%', '%VALUE3%') 

but i was getting error as IN doesn’t work with Like.

There is two way you can write this query.

Method 1 :

Add multiple Like statement, each for every word to be excluded.

Select * from TABLE
where Column NOT Like '%VALUE1%'
AND Column NOT Like '%VALUE2%'
AND Column NOT Like '%VALUE3%' 

Method 2 :

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
TABLE
WHERE
Column NOT REGEXP 'VALUE1|VALUE2|VALUE3' 

Search set of words in a column :

SELECT
*
FROM
TABLE
WHERE
Column REGEXP 'VALUE1|VALUE2|VALUE3'