SQL FTS and comparison statements

Question

Short story. I am working on a project where I need to communicate with SQLite database. And there I have several problems:

There is one FTS table with nodeId and nodeName columns. I need to select all nodeIds for which nodeNames contains some text pattern. For instance all node names with "Donald" inside. Something similar was discussed in this thread. The point is that I can't use CONTAINS keyword. Instead I use MATCH. And here is the question itself: how should this "Donald" string be "framed"? With '*' or with '%' character? Here is my query:

SELECT * FROM nodeFtsTable WHERE nodeName MATCH "Donald"

Is it OK to write multiple comparison in SELECT statement? I mean something like this:

SELECT * FROM distanceTable WHERE pointId = 1 OR pointId = 4 OR pointId = 203 AND distance<200

I hope that it does not sound very confusing. Thank you in advance!

Answer 1

Edit: Sorry, I missed the fact that you are using FTS4. It looks like you can just do this:

SELECT * FROM nodeFtsTable WHERE nodeName MATCH 'Donald'

Here is relevant documentation.

No wildcard characters are needed in order to match all entries in which Donald is a discrete word (e.g. the above will match Donald Duck). If you want to match Donald as part of a word (e.g. Donalds) then you need to use * in the appropriate place:

SELECT * FROM nodeFtsTable WHERE nodeName MATCH 'Donald*'

If your query wasn't working, it was probably because you used double quotes.

From the SQLite documentation:

The MATCH operator is a special syntax for the match() application-defined function. The default match() function implementation raises an exception and is not really useful for anything. But extensions can override the match() function with more helpful logic.

FTS4 is an extension that provides a match() function.

Yes, it is ok to use multiple conditions as in your second query. When you have a complex set of conditions, it is important to understand the order in which the conditions will be evaluated. AND is always evaluated before OR (they are analagous to mathematical multiplication and addition, respectively). In practice, I think it is always best to use parentheses for clarity when using a combination of AND and OR:

--This is the same as with no parentheses, but is clearer:
SELECT * FROM distanceTable WHERE 
    pointId = 1 OR 
    pointId = 4 OR 
    (pointId = 203 AND distance<200)

--This is something completely different:
SELECT * FROM distanceTable WHERE 
    (pointId = 1 OR pointId = 4 OR pointId = 203) AND
    distance<200