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

score 1 · Answer 2 · answered Apr 02 '23 at 15:46

The best solution is the usability of a full-text search engine like SQLite FTS4 or FTS5 which is design for efficient searching of large datasets.

You can create a virtual table that indexes the text columns you would want to search, and then its used SQL to search for words or phrases in the columns. It will handle tokenization, stemming and other text processing tasks for you, to manually list them out.

Use FTS like this to search for the word one in the english_meaining of your kanji:

Create a virtual table for the english_meaning column:

CREATE VIRTUAL TABLE kanji_fts USING FTS5(english_meaning);

Populate a virtual table with data from kanji table:

INSERT INTO kanji_fts(rowid, english_meaning) SELECT rowid, english_meaning FROM kanji;

Search for the word one in the table:

SELECT * FROM kanji WHERE rowid IN (SELECT rowid FROM kanji_fts WHERE english_meaning MATCH 'one');

It will return all the rows from kanji and the english_meaning column will contain the one as a separate word.

FTS allows you to do searches with minimal code and high performance.

This worked for me:

// Create the virtual table if it doesn't exist
db.execSQL("CREATE VIRTUAL TABLE IF NOT EXISTS kanji_fts USING FTS5(english_meaning);");

// Populate the virtual table with data if it's empty
Cursor cursor = db.rawQuery("SELECT COUNT(*) FROM kanji_fts", null);
cursor.moveToFirst();
int count = cursor.getInt(0);
if (count == 0) {
    db.execSQL("INSERT INTO kanji_fts(rowid, english_meaning) SELECT rowid, english_meaning FROM kanji;");
}

// Perform a search on the virtual table
String query = "SELECT * FROM kanji WHERE rowid IN (SELECT rowid FROM kanji_fts WHERE english_meaning MATCH ?) ORDER BY stroke_count";
String[] selectionArgs = new String[]{"one"};
cursor = db.rawQuery(query, selectionArgs);

// Process the results
if (cursor != null) {
    cursor.moveToFirst();
}
return cursor;

SQLite match against words

2 Answers2