Contents - Index

SQLite Collation


There is one "Gotcha" in Sqlite (particularly for our European users) that I would almost call a bug.Case sensitively works differently in Sqlite than you would probably expect. Everywhere in Sqlite that refers to case, only applies to 7bit iso8859. Basically this means only a-z and A-Z

When I posed the question as to why in one of the Sqlite support forums the answer went something like:
 

It is an acknowledged "bug" in SQLite which is intentionally not fixed in order to keep the library size small. The SQLite developers feel that adding full Unicode case tables would inflate its size unreasonably.

 
What does this mean?

Basically it means that you need to be aware that any character above ascii 127 will always be case sensitive when using sqlite native handling case routines

For example, normally when ignoring case you would expect the following two characters to be the same:
 
üÜ
 
However, if you used a query expression of "like '%ü%'" (like is supposed to be case insensitive) it would only return a match for the lower case ü

Yet a similar query of "like '%a%'" would return anything that contained "a" or "A"

Similarly for the native sqlite Upper() function. The result of Upper('ü') is ü instead of the expected Ü

This is incongruent with native GSAK behaviour in both the existing database and macro language, as it supports the full 8bit Ansi character set when dealing with case. Anywhere in GSAK where case is ignored (all the "contains" in a filter dialog, Upper() macro function, Replace() macro function etc)

One of the great strengths of Sqlite (and a significant reason why I chose this SQL database engine) is that it does allow you to "roll your own" functions to be used natively in any Sqlite query.

GSAK two functions in sqlite to address this issue:

g_Contains(sFind,sData) : boolean
g_Upper(sData) : string

g_contains will allow you to do a "GSAK" case insensitive search to see if the sFind string is matched anywhere in sData). Think of this as a loose replacement for the sqlite "like"

For example to get a list of caches that contain the character "ü" in the cache name and have the result be case insensitive
 

Select * from caches where g_Contains('ü',name)

  
Use these two functions in your sqlite queries when you want case sensitivity handled the same way as GSAK currently does now - which for many I feel is the more intuitive method.

Similarly, if you are wanting your data sorted in a case insensitive manner that is consistent with GSAK case insensitive sorting, then use the g_Upper() function in your order by clause:
 

Select * from caches order by g_upper(name)