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'