SQLite Forum

Unary NOT in FTS5 MATCH query
Login

Unary NOT in FTS5 MATCH query

(1.2) By ACMCMC (acmcmc) on 2021-08-04 16:04:54 edited from 1.1 [link] [source]

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?

(2) By Martijn (Martijn81) on 2021-08-04 17:02:15 in reply to 1.2 [source]

I would say:

MATCH '* NOT stuffidontneed'

But that probably won't work..

(3) By ACMCMC (acmcmc) on 2021-08-04 21:33:49 in reply to 2 [link] [source]

No, it doesn't work either... :(

(4.2) By Perki (perkix) on 2022-03-02 12:33:34 edited from 4.1 in reply to 1.2 [link] [source]

Does anyone found a solution here? I ended up in adding a common word at the end of all texts before insert (that I have to trim afterwards).

This word could be seen as a "ALL" match .. Then it allows to perform WHERE MATCH "ALL" NOT "query 2"

I don't like this solution, maybe there is a hidden feature in FTS5 to match all ?

Example to get all messages that do not include "C". (The ALL word is "..")

INSERT INTO table1 (messages) VALUES ("A .."), ("B .."), ("C ..");  

SELECT messages FROM table1_fts WHERE messages MATCH ".." NOT "C";
// then I strip the last 3 characters (ALL.length()+1) of each result item 

(5) By mgr (mgrmgr) on 2022-03-02 16:35:37 in reply to 4.2 [link] [source]

No direct solution, but you can at least avoid the trimming of your found text by

  • add another dummy column to your FTS5 table
  • fill that column with your ALL_WORD for every row
  • do a match like
    SELECT real_text_column FROM fts_table WHERE fts_table MATCH '(dummy: ALL_WORD) NOT <your-search>';
    

Restricting your 'find-all' to the dummy column, the ALL_WORD can be a single character, no matter if it exists in the real text as well.

(6) By Perki (perkix) on 2022-03-04 15:17:54 in reply to 5 [link] [source]

Thanks @mgr at least it will look nicer ;)

(7) By art (artsqliteuser) on 2023-07-19 06:03:08 in reply to 5 [link] [source]

This thread was super helpful, thanks to OP and answers!

I ended up achieving unary NOT by negating an inner select without NOT...

SELECT * FROM t WHERE id NOT IN (SELECT id FROM t WHERE t MATCH '<value_to_not_find>')

I did not analyze any perf comparisons... just wanted to do something without adding a column... so adding col may be faster.