Android Sqlite FTS3 how to select words that starts with?

Question

For example, if i have these records

word

AAA

AAB

AAC

BAA AA

With a normal table i would use sql like

select * from table where word like 'AA%'order by H collate nocase asc

How do i select with FTS3 table instead? Also i would like to know if FTS3 will still have better performance than normal table with this kind of query?

Answer 1

How do i select with FTS3 table instead?

Quoting the documentation:

An FTS table may be queried for all documents that contain a specified term (the simple case described above), or for all documents that contain a term with a specified prefix. As we have seen, the query expression for a specific term is simply the term itself. The query expression used to search for a term prefix is the prefix itself with a '*' character appended to it.

The documentation also gives a sample:

-- Query for all documents containing a term with the prefix "lin". This will match
-- all documents that contain "linux", but also those that contain terms "linear",
--"linker", "linguistic" and so on.
SELECT * FROM docs WHERE docs MATCH 'lin*';