CODEWITHSUNDEEP
objective-csqlite
yellow
yellow

Reputation: 702

How to select sqlite order by char and number key?

Some value of col in sqlite database:

a1b2
a2b3
a1b10
a10b1

if use order by in sql it will be:

a10b1
a1b10
a1b2
a2b3

I want it like NSNumericSearch of objc like this:

a1b2
a1b10
a2b3
a10b1

How do I write the SQL statements?

Upvotes: 0

Views: 158

Answers (1)

rmaddy
rmaddy

Reputation: 318874

Start by creating a custom collating function:

int compare_natural(void *data, int len1, const void *str1, int len2, const void *str2) {
    if (str1 && len1 > 0 && str2 && len2 > 0) {
        NSString *s1 = [[NSString alloc] initWithBytesNoCopy:(void *)str1 length:len1 encoding:NSUTF8StringEncoding freeWhenDone:NO];
        NSString *s2 = [[NSString alloc] initWithBytesNoCopy:(void *)str2 length:len2 encoding:NSUTF8StringEncoding freeWhenDone:NO];

        // The use of NSNumericSearch is required for your need.
        // The others are options depending on your needs
        NSComparisonResult res = [s1 compare:s2 options:NSCaseInsensitiveSearch | NSNumericSearch | NSDiacriticInsensitiveSearch];

        return res;
    } else if (str1 && len1 > 0 && (!str2 || len2 == 0)) {
        return -1; // empty strings to the end of the list (optional)
    } else if (str2 && len2 > 0 && (!str1 || len1 == 0)) {
        return 1; // empty strings to the end of the list (optional)
    } else {
        return 0;
    }
}

Then register the custom collator. This needs to be done after you open the database.

// dbRef is your sqlite3 database reference
int rc = sqlite3_create_collation(dbRef, "BYNUMBER", SQLITE_UTF8, NULL, &compare_natural);

Then update your query so it ends with "COLLATE BYNUMBER"

SELECT some_col ORDER BY some_col COLLATE BYNUMBER;

Upvotes: 3

Related Questions