Hi Jon,

I think you describe not enough to understand what is wrong and what is expected

Hi Ruslan,

I have sample database with 4 entries. There are two indexed TEXT fields I want to a Regex search on. All 4 records have the term "ocean solutions" in the keywords field (and my app finds them if I just do a regex search for them). 2 of the records also have the term "ocean solutions vu" in the notes field. So in the search above I expect to find two records (of the 4). Instead I get 0 hits.

I get the same (incorrect) result in VStudio.

I'm trying to reproduce it but cant. It works as expected for me.
Probably it is syntax issue because string literals must be wrapped by '' (apostrophes ) - not `` (grave accents).

Please, check it twice and send me your db and exact query to check on my side if it doesn't work.

So, original query is:

SELECT * FROM thereferences WHERE keywords REGEX 'ocean solutions' AND NOT (notes REGEX 'ocean solutions vu')

Pay attention that notes are set to null for first two records. You should check it as well. Because almost any operation against NULL produces NULL - neither TRUE nor FALSE.

SELECT * FROM thereferences 

WHERE 

keywords REGEX 'ocean solutions' AND 

( notes is null OR NOT (notes REGEX 'ocean solutions vu') )

Thank you, Ivan.