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?

Answer 1

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,')