我通过创建自己的SQLite函数来解决这样一个问题。
基本思路是您的查询变为:
NSString *queryStatement = [[NSString alloc] initWithFormat:@"SELECT ID, ARABIC, ARABICMEANING, FRENCHINARABIC, FRENCH, BEGINARABIC, BEGINFRENCH, ISFAVORITE FROM DictionaryDB WHERE contains(FRENCHINARABIC, '%@')", searchedWord];
这里的contains
是你自定义的函数。
首先,你需要编写一个C函数来实现contains
SQL函数:
void contains(sqlite3_context *context, int argc, sqlite3_value **argv) {
BOOL res = NO;
if (argc < 2) {
res = NO;
} else {
char *textstr = (char *)sqlite3_value_text(argv[0]);
char *substr = (char *)sqlite3_value_text(argv[1]);
if (textstr && substr) {
NSString *text = [NSString stringWithCString:textstr encoding:NSUTF8StringEncoding];
NSString *sub = [NSString stringWithCString:substr encoding:NSUTF8StringEncoding];
NSRange range = [text rangeOfString:sub options:NSCaseInsensitiveSearch | NSDiacriticInsensitiveSearch | NSWidthInsensitiveSearch];
if (range.location != NSNotFound) {
res = YES;
}
}
}
sqlite3_result_int(context, res ? 1 : 0);
}
当您打开数据库连接时,需要注册此函数:
// dbRef is your database reference
int res = sqlite3_create_function(dbRef, "contains", 2, SQLITE_UTF8, NULL, &contains, NULL, NULL);
if (res != SQLITE_OK) {
NSAssert1(0, @"Error: failed to create function in the database: '%s'.", sqlite3_errmsg(dbRef));
}
顺便提一下 - 使用stringWithFormat:
创建查询是一个不好的做法。您应该考虑使用sqlite3_bind_xxx
函数来正确地绑定查询值。如果值中包含任何引号或其他特殊字符,则使用stringWithFormat:
将失败。使用sqlite3_bind_xxx
函数可以正确地引用和转义值。