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!

dan1111 · Accepted Answer · 2012-10-24T08:56:25.223

2

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

edited Oct 24 '12 at 08:56

answered Oct 24 '12 at 07:42

dan1111

6,576
2
18
29

Thank you. In my case LIKE is unacceptable - it will take too many time to find necessary entries. – besworland Oct 24 '12 at 07:45
@besworland, sorry, my first answer was mistaken. Please see the edited version. – dan1111 Oct 24 '12 at 07:50
Thank you. But will I get back all the entries with "Donald" in this case? In this document it is written that '*' must be placed somewhere in a phrase? – besworland Oct 24 '12 at 07:53
@besworland, no wildcards are needed as long as you want to match `Donald` as a separate word. Please see the clarification in the answer. – dan1111 Oct 24 '12 at 08:08
Exactly, here is the point, I want all the words with Donald. It can be McDonald's or DonaldDuck or whatever. – besworland Oct 24 '12 at 08:18
@besworland, then you should use `'*Donald*'` – dan1111 Oct 24 '12 at 08:22
Can you give me any tips for my second question? – besworland Oct 24 '12 at 08:47
@besworland, sorry, I had mentioned that, but I accidentally deleted it when editing the question. I have updated it again and given more detail. – dan1111 Oct 24 '12 at 08:56

SQL FTS and comparison statements

1 Answers1