3

The SQLite FTS5 docs say that search queries such as SELECT ... WHERE MATCH '<query1> NOT <query2>' are supported, but it looks like there's no support for the unary NOT operator.

For example, if I want to search for everything that doesn't match <query>, I cannot use MATCH 'NOT <query>'. I would have to use NOT MATCH '<query>', which is a completely different thing (the FTS5 module never gets to see the NOT operator, as it is outside the quotation marks). Only the text inside the quotation marks is the search query.

I need to find a way to use an unary NOT operator inside the search query. I can't use it outside, because I only get to control the search query text, and not the rest of the SQL statement.

A possible approach I've thought of would be to find a search query that matches anything, and do MATCH '<match_anything> NOT <query>'. However, I've found no way to match everything in a search query.

Can you think of a way to have the behaviour of the unary NOT operator inside the search query?

Aldan Creo
  • 686
  • 1
  • 5
  • 14

1 Answers1

0

Try this ..

SELECT * FROM docs
WHERE ROWID NOT IN ( 
  SELECT ROWID FROM docs WHERE content MATCH '<query>'
)
cakyus
  • 776
  • 1
  • 6
  • 13
  • Good idea! However, as I stated in my original question, I need to find a way to use an unary `NOT` operator _**inside the search query**_. Here, it is outside, so in my case, it's not useful... However, in a general case, it does indeed work as a workaround! – Aldan Creo Dec 15 '21 at 15:07