如何处理iOS SQLite中的重音字符？ [英] How to deal with accented characters in iOS SQLite?

问题描述

我需要执行对case和accents不敏感的SELECT查询。为了演示目的，我创建了一个这样的表：

I need to perform a SELECT queries that are insensitive to case and accents. For demo purposes, I create a table like that:

create table table
(
  column text collate nocase
);

insert into table values ('A');
insert into table values ('a');
insert into table values ('Á');
insert into table values ('á');

create index table_cloumn_Index
  on table (column collate nocase);

然后，我在执行以下查询时得到这些结果：

Then, I get those results when executing the following queries:

SELECT * FROM table WHERE column LIKE 'a';
> A
> a

SELECT * FROM table WHERE column LIKE 'á';
> á

SELECT * FROM table WHERE column LIKE 'Á';
> Á

我如何解决这个问题，以便以下任何查询的结果如下：

How can I fix that so the results for any of the following queries be like that:

> A
> a
> Á
> á

顺便说一句，sqlite在iOS上运行。

The sqlite is running on iOS, by the way.

提前致谢，

推荐答案

两种基本方法：

1. 您可以在表格中创建第二列，其中包含不带国际字符的字符串。此外，在对此辅助搜索列进行搜索之前，您还应该从正在搜索的字符串中删除国际字符（这样您就可以将非国际字符与非国际字符进行比较）。

1. You can create a second column in the table which contains the string without the international characters. Furthermore, before doing a search against this secondary search column, you should also remove international characters from the string being search for, too (that way you are comparing non-international to non-international).

这是我用来转换国际字符的例程：

This is the routine I use to convert the international characters:

NSData *data = [string dataUsingEncoding:NSASCIIStringEncoding allowLossyConversion:YES];
string = [[NSString alloc] initWithData:data encoding:NSUTF8StringEncoding];

你也可以用以下代码替换重音字符：

You could also replace the accented characters with:

NSMutableString *mutableString = [string mutableCopy];
CFStringTransform((__bridge CFMutableStringRef)mutableString, NULL, kCFStringTransformStripCombiningMarks, NO);

顺便说一句，如果您需要对结果进行排序，您还可以对此辅助搜索字段进行排序而不是主要字段，这将避免源自SQLite无法对国际字符进行排序的问题。

By the way, if you need to sort your results, you can also sort upon this secondary search field instead of the main field, which will avoid problems stemming from SQLite's inability to sort the international characters, either.

您也可以创建自己的非重音C function（在你的班级的 @implementation 之外定义这个C函数）：

You can alternatively create your own "unaccented" C function (define this C function outside the @implementation for your class):

void unaccented(sqlite3_context *context, int argc, sqlite3_value **argv)
{
    if (argc != 1 || sqlite3_value_type(argv[0]) != SQLITE_TEXT) {
        sqlite3_result_null(context);
        return;
    }

    @autoreleasepool {
        NSMutableString *string = [NSMutableString stringWithUTF8String:(const char *)sqlite3_value_text(argv[0])];
        CFStringTransform((__bridge CFMutableStringRef)string, NULL, kCFStringTransformStripCombiningMarks, NO);
        sqlite3_result_text(context, [string UTF8String], -1, SQLITE_TRANSIENT);
    }
}

然后你可以定义一个将调用它的SQLite函数C函数（打开数据库后调用此方法，直到关闭该数据库才会生效）：

You can then define a SQLite function that will call this C-function (call this method after you open the database, which will be effective until you close that database):

- (void)createUnaccentedFunction
{
    if (sqlite3_create_function_v2(database, "unaccented", 1, SQLITE_ANY, NULL, &unaccented, NULL, NULL, NULL) != SQLITE_OK)
        NSLog(@"%s: sqlite3_create_function_v2 error: %s", __FUNCTION__, sqlite3_errmsg(database));
}

完成后，你现在可以使用这个新的 SQL中的unaccented 函数，例如：

Having done that, you can now use this new unaccented function in SQL, e.g.:

if (sqlite3_prepare_v2(database, "select a from table where unaccented(column) like 'a'", -1, &statement, NULL) != SQLITE_OK)
    NSLog(@"%s: insert 1: %s", __FUNCTION__, sqlite3_errmsg(database));

这篇关于如何处理iOS SQLite中的重音字符？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！