iOS: trouble when querying swedish letters with sqlite3

Question

I am retrieving ingredients from an sqlite database and inserting them to my table view. This is done every time the user edits a search bar (UISearchBar) located on screen.

QueryMethod:

-(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;

}

This is how I format my queries and update the 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];

    }
}

As you can see in formatString I am trying to order the result and ignore the case of the string

ORDER BY namn COLLATE NOCASE

This works fine, but with the swedish letters Å, Ä, Ö, the case ignoring fails.

If I search for 'Ål' I am not getting the same result as i do when i search får 'ål'. In other words, the ingredient 'Ål' does not show when i search for 'ål'

My Question: How can i have SQLite ignore case, with foreign(Swedish) letters included?

Hopes the above makes sense,

Thanks.

EDIT

ended up with the following approach:

-(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];

}

score 1 · Accepted Answer · edited May 23 '17 at 11:49

1

From the 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.

Your best bet is probably to compile your own SQLite with ICU extensions.

edited May 23 '17 at 11:49

Community

1
1

answered Aug 16 '13 at 11:37

Joachim Isaksson

176,943
25
281
294

Thanks, great answer. Now i know. However, that seems a little overkill for me. Do you perhaps know any other workaround for this matter? if there is any? – Eyeball Aug 16 '13 at 11:47
1

@Eyeball You could always create your own `COLLATE` function like in [this answer](http://stackoverflow.com/questions/16806570/how-do-i-compare-characters-in-custom-sqlite-collation-in-objective-c) – Alexander Aug 16 '13 at 11:51

score 0 · Answer 2 · answered Aug 16 '13 at 11:50

0

in the Query Method, instead of [predicateString UTF8String] try using

[predicateString cStringUsingEncoding:NSISOLatin1StringEncoding]

or

[predicateString cStringUsingEncoding:NSUTF8StringEncoding]

answered Aug 16 '13 at 11:50

instaable

3,449
2
24
31

iOS: trouble when querying swedish letters with sqlite3

2 Answers2