iOS:使用 sqlite3 查询瑞典字母时出现问题

Question

我正在从 sqlite 数据库中检索成分并将它们插入到我的 TableView 中。每次用户编辑位于屏幕上的搜索栏 (UISearchBar) 时都会执行此操作。

查询方法:

-(NSArray*)sqliteInfo:(NSString*)predicateString{
    NSMutableArray *retval = [[[NSMutableArray alloc] init] autorelease];
    sqlite3_stmt *statement;
    if (sqlite3_prepare_v2(_database, [predicateString UTF8String], -1, &statement, nil)
        == SQLITE_OK) {
        while (sqlite3_step(statement) == SQLITE_ROW) {
            char *nameChars = (char *) sqlite3_column_text(statement, 0);
            NSString *name = [[NSString alloc] initWithUTF8String:nameChars];

            sqlite *info = [[sqlite alloc]
                                    initWithName:name carbs:nil fat:nil kcal:nil];
            [retval addObject:info];
            [name release];
        }
        sqlite3_finalize(statement);
    }
    return retval;

}

这就是我格式化查询和更新 TableView 的方式:

-(void)searchBar:(UISearchBar *)searchBar textDidChange:(NSString *)searchText{

    if (searchText.length < 2) {
        return;
    } else {

    [self.nutritionList removeAllObjects];

    NSString *formatString = [[@"'%"stringByAppendingString:searchText]stringByAppendingString:@"%'   ORDER BY namn COLLATE NOCASE"];
    NSArray *array = [[NutritionsDB database] sqliteInfo:[NSString stringWithFormat:@"Select namn from    livsmedel where namn like %@", formatString ]];
    for (sqlite *info in array) {
        [self.nutritionList addObject:info.name];
    }
    [self.tableView reloadData];

    }
}

正如您在 formatString 中看到的，我正在尝试对结果排序并忽略字符串的大小写

ORDER BY namn COLLATE NOCASE

这工作正常，但对于瑞典字母 Å、Ä、Ö，忽略大小写失败。

如果我搜索“Ål”，我不会得到与搜索 får“ål”时相同的结果。换句话说，当我搜索“ål”时，成分“Ål”没有显示

我的问题:我怎样才能让 SQLite 忽略大小写，包括外国(瑞典语)字母？

希望以上是有道理的，

谢谢。

编辑

最终采用了以下方法:

-(void)searchBar:(UISearchBar *)searchBar textDidChange:(NSString *)searchText{


    if (searchText.length < 2) {
        return;
    } else {

        NSString *firstChar = [searchText substringWithRange:NSMakeRange(0, 1)];

        if([firstChar isEqualToString:@"å"] ||
           [firstChar isEqualToString:@"Å"] ||
           [firstChar isEqualToString:@"ä"] ||
           [firstChar isEqualToString:@"Ä"] ||
           [firstChar isEqualToString:@"ö"] ||
           [firstChar isEqualToString:@"Ö"]){

            NSString *lowerString = [firstChar lowercaseString];
            NSString *upperString = [firstChar uppercaseString];

            NSString *newSearchText = [searchText stringByReplacingCharactersInRange:NSMakeRange(0, 1) withString:lowerString];
            NSString *newSearchText_upper = [searchText stringByReplacingCharactersInRange:NSMakeRange(0, 1) withString:upperString];

            NSString *formatString = [[[[[@"'%"stringByAppendingString:newSearchText]stringByAppendingString:@"%'"]
                                        stringByAppendingString:@" OR namn like'%"]stringByAppendingString:newSearchText_upper]stringByAppendingString:@"%' ORDER BY namn COLLATE NOCASE"];

            NSLog(formatString);
            [self.nutritionList removeAllObjects];
            NSArray *array = [[NutritionsDB database] sqliteInfo:[NSString stringWithFormat:@"Select namn from livsmedel where namn like %@", formatString ]];
            for (sqlite *info in array) {
                [self.nutritionList addObject:info.name];
            }
            [self.tableView reloadData];
            return;
            }

        }

        [self.nutritionList removeAllObjects];

        NSString *formatString = [[@"'%"stringByAppendingString:searchText]stringByAppendingString:@"%' ORDER BY namn COLLATE NOCASE"];
        NSArray *array = [[NutritionsDB database] sqliteInfo:[NSString stringWithFormat:@"Select namn from livsmedel where namn like %@", formatString ]];
        for (sqlite *info in array) {
            [self.nutritionList addObject:info.name];
        }
        [self.tableView reloadData];

}

Answer 1

来自reference manual ;

NOCASE - The same as binary, except the 26 upper case characters of ASCII are folded to their lower case equivalents before the comparison is performed. Note that only ASCII characters are case folded. SQLite does not attempt to do full UTF case folding due to the size of the tables required.

您最好的选择可能是 compile your own SQLite与 ICU extensions .