Reputation: 6976
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:
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?
Upvotes: 4
Views: 983
Reputation: 1
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;
Upvotes: 1
Reputation: 1019
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,')
Upvotes: 1