3

I want search those rows that do not contain "rajasthan" in the state field using match against in MySql.

My query is this:

SELECT * from member where MATCH(state) AGAINST('-rajasthan' IN BOOLEAN MODE)

However, it returns an empty set of result.

What is the problem with this?

Brian Webster
  • 30,033
  • 48
  • 152
  • 225
Deepa
  • 1,201
  • 5
  • 15
  • 23

2 Answers2

11

I believe this will do the trick:

SELECT 
  *
FROM 
  Member
WHERE 
  NOT MATCH (state) AGAINST ('+rajasthan' IN BOOLEAN MODE)

Source: "Show all except" in MySQL Boolean Full-Text Searches

Beware - I believe this will do a full table scan and this query will not benefit from the FullText index as you might hope.

As to your empty result:

Note: The - operator acts only to exclude rows that are otherwise matched by other search terms. Thus, a boolean-mode search that contains only terms preceded by - returns an empty result. It does not return “all rows except those containing any of the excluded terms.”

Source

Community
  • 1
  • 1
Brian Webster
  • 30,033
  • 48
  • 152
  • 225
  • can u suggest me alternate of query for being fast – Deepa May 12 '11 at 04:33
  • You may consider this query: Select * FROM Member where State not like '%rajasthan%' However, my gut tells me this may be the same speed or slower. May be worth a try though – Brian Webster May 12 '11 at 14:22
-1

The - operator acts only to exclude rows that are otherwise matched by other search terms. Thus, a boolean-mode search that contains only terms preceded by - returns an empty result. It does not return “all rows except those containing any of the excluded terms.”

So try appending to your query OR 1 = 1 so that the query to return all your records without MATCH operator

Tudor Constantin
  • 26,330
  • 7
  • 49
  • 72
  • OR 1 = 1 will also return records that you try to exclude by MATCH(state) AGAINST('-rajasthan' IN BOOLEAN MODE) – aivarsk Apr 05 '18 at 08:08