SQLite match against words

Question

I have a sqlite database with a table named kanji which has a column named english_meaning. I would like to query for a "word".

This is my existing query and code:

public Cursor fetchAllKanjiWithQuery(String input) {
    input = "%" + input + "%";
    String query = "SELECT * FROM kanji WHERE literal LIKE ? OR radicals LIKE ? OR english_meaning LIKE ? OR kun_reading LIKE ? OR romaji LIKE ? ORDER BY stroke_count";
    String[] selectionArgs = new String[]{input, input, input, input, input};
    Cursor cursor = db.rawQuery(query, selectionArgs);
    if (cursor != null) {
        cursor.moveToFirst();
    }
    return cursor;
}

With the input being "one", results where english_meaning is "bone" or "oneself" would match.

However, I'm only interested in strings with the word "one". For example, valid matches should include:

"one, a radical character"
"choose from one"
"one counter"

Namely, "one" can appear at the beginning or end of the string, it's separated from other characters by spaces. If possible, "one" should also be allowed to be followed by punctuation character such as "," and ".".

Currently, my query with LIKE and % input % doesn't give the desired result. How can this be fixed?

You should look into using full text search. The `LIKE` operator is going to fall short here. — Tim Biegeleisen, Jan 02 '18 at 03:08
See here for reference of FTS https://www.sqlite.org/fts3.html — NineBerry, Jan 02 '18 at 03:12
Thanks for the pointer! Looks like full text search has what I need @TimBiegeleisen — waylonion, Jan 02 '18 at 03:48
@wayway I can give you a query using `LIKE`, but it will be very ugly. FTS is probably a better long term bet. — Tim Biegeleisen, Jan 02 '18 at 03:50
@TimBiegeleisen for references sake, could you show me how I could achieve it with `LIKE` then? I won't be using it since it appears that FTS will have better performance and flexibility. Thanks! — waylonion, Jan 02 '18 at 17:51
Possible duplicate of [How do I use regex in a SQLite query?](https://stackoverflow.com/questions/5071601/how-do-i-use-regex-in-a-sqlite-query) — AxelH, Jan 03 '18 at 12:21

score 1 · Answer 1 · answered Jan 03 '18 at 10:59

Why not do it like this:

SELECT *
 from kanji  
 where (english_meaning = 'one'
    or english_meaning = 'one.'
    or english_meaning = 'one,'
    or english_meaning like 'one %'
    or english_meaning like '% one %'
    or english_meaning like '% one'
    or english_meaning like '% one.'
    or english_meaning like '% one,')

SQLite match against words

1 Answers1