Вопрос по sqlite, ios – Как бороться с акцентированными символами в iOS SQLite?

11

Error: User Rate Limit Exceeded

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

Error: User Rate Limit Exceeded

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

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

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

Error: User Rate Limit Exceeded

> A
> a
> Á
> á

Error: User Rate Limit Exceeded

Error: User Rate Limit Exceeded

Ваш Ответ

3   ответа
18

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

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.

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);
    }
}

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));
}

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));
Error: User Rate Limit ExceededEXC_BAD_ACCESS (code=2, address=0x0)Error: User Rate Limit Exceeded
Error: User Rate Limit Exceeded
Error: User Rate Limit Exceeded Leandro Alves
Error: User Rate Limit Exceededchat.stackoverflow.com/rooms/41329/…
4

like() functionsLIKE

SQLite only understands upper/lower case for ASCII characters by default. The LIKE operator is case sensitive by default for unicode characters that are beyond the ASCII range. For example, the expression 'a' LIKE 'A' is TRUE but 'æ' LIKE 'Æ' is FALSE.

i in Turkey which is not I but a dotted İ, and the lower-case of I is a dot-less ı

Error: User Rate Limit Exceeded Leandro Alves
1

static void myLow(sqlite3_context *context, int argc, sqlite3_value **argv)
{
    NSString* str = [[NSString alloc] initWithUTF8String:
                            (const char *)sqlite3_value_text(argv[0])];
    const char* s = [[str lowercaseString] UTF8String];
    sqlite3_result_text(context, s, strlen(s), NULL);
    [str release];
}

// call it once after opening db
sqlite3_create_function(_db, "myLow", 1, SQLITE_UTF8,NULL, &myLow, NULL, NULL);

SELECT * FROM table WHERE column LIKE 'a'

SELECT * FROM table WHERE myLow(column) LIKE 'a'

Похожие вопросы