2

I've tried several methods from here:

SQLite FTS example doesn't work

and here:

Full text search example in Android (best tutorial so far i think)

However, my search returns 0 results!

Here is what I've tried:

   String key = "a";
        Cursor c = db.query(true, "texts_virtual",
                new String[]{"id","title_normalized"},
                "title_normalized MATCH '"+key+"'",
                null, null, null, null, null);

= 0 Results;

 String query = "a";
    String[] params = {"%" +query+ "%"};

    Cursor c = db.rawQuery("SELECT * FROM texts_virtual WHERE title_normalized MATCH ?", params);

= 0 Results too

I know that the virtual table is correctly working because I can do this:

String queryText = "a"; //here i test other texts and they worked too
        String query = "select * from texts_virtual where title_normalized like ? order by number";
        String[] params = {"%" + queryText + "%"};
        Cursor c = db.rawQuery(query, params);

so this prove that the texts_virtual is working, what is not working are the queries, but I don't know why, not error, nothing, just 0 results.

Also after I make it work, I'm planning to use multiple terms search in 2 columns

user type "WordA WordB WordC"

it search for each word in the 2columns and return the results, but this if for a future task....

Edit

Table Code Creation:

CREATE TABLE texts (id INTEGER PRIMARY KEY AUTOINCREMENT, title_normalized....);

INSERT INTO texts (id, titulo_normalized...) VALUES (1, 'aaaaaa', ...);

and go on for more inserts, and at the end the virtual creation

CREATE VIRTUAL TABLE texts_virtual USING fts4(content="texts", id, title_normalized, ..other fields);

i can query texts_virtual using LIKE but not MATCH, match return 0 results =/

Edit 2 how the table looks:

Table: texts_virtual
----------------------------
id --- title_normalized
--------------------------
1  --- aaaaaaaaab
2  --- abbbbbbbbb
3  --- bbbbbabbbb
4  --- bbbbbbbbbb
Community
  • 1
  • 1
Senhor Obvio
  • 71
  • 8
  • 1
    table creation code? (is fts4 even supported on android? or is it only fts3?) – njzk2 Jul 31 '15 at 16:31
  • @njzk2 added, the fts4 in android i dont know, i will search if its only the fts4 or 3 supported) – Senhor Obvio Jul 31 '15 at 16:46
  • MATCH and LIKE work differently. Neither "a" nor "WordA" are in the example data you've shown. – CL. Jul 31 '15 at 17:02
  • @CL. i know, the problem is the MATCH is not returning the results, it just return 0, but if i do the same search using LIKE, it return the results =/, and the WordA was just a example of what i want to do in future – Senhor Obvio Jul 31 '15 at 17:09
  • And what example row do you want to find? – CL. Jul 31 '15 at 17:10
  • @CL. i have rows title_normalized and description_normalized, and search inside this two column the query text typed in the EditText (i know how to send, my problem is just the MATCH not working =( ) – Senhor Obvio Jul 31 '15 at 17:17
  • @njzk2 fts4 is support because the external database (using fts4(content="texts")) not work in the fts3, so i suppose that fts4 is working in Android – Senhor Obvio Jul 31 '15 at 17:24
  • Show the exact row in which you expect to find "a". – CL. Aug 01 '15 at 05:14
  • @CL. added, see the edit2 please! – Senhor Obvio Aug 01 '15 at 17:59

2 Answers2

2

The FTS module searches for words (where the exact definition depends on the tokenizer used), or at best for words with a prefix.

MATCH words as designed; it does not find "a" because there is no word "a" in your data.

If you want to find substrings inside words, you must use LIKE.

CL.
  • 173,858
  • 17
  • 217
  • 259
  • of course there is a word a, i dont get it, my database has a plenty of a words there o0.... – Senhor Obvio Aug 03 '15 at 06:17
  • There is no word "a" in the example data you've shown. And as I've mentioned, the tokenizer might have a different definition of "word". – CL. Aug 03 '15 at 08:03
  • and how to explain if i added one row with just one 'a' as value and nothing match? still a "different concept" of word for the FTS? – Senhor Obvio Aug 03 '15 at 17:47
1

You are using % as a joker. In FTS requests, You have to use * instead.

LIKE "%word%"

MATCH "*word*"

I've noticed that for very short words (less than 3 letters), LIKE is faster than MATCH. For longer words, MATCH is faster.

Thomas
  • 401
  • 1
  • 6
  • 11