You may know that SQLite can use ICU extension to use the International Components for Unicode library to handle unicode properly. If the extension is enabled, you get unicode aware functions. For our InternalSQLiteLibraryMBS module, we add the capability to automatically use the libraries.
Upper and Lower
To map between lower and upper case, you can use those function and they should work perfectly for all unicode characters. But there are some language differences and so you may want to pass the locale specification with the function call.
lower(‘I’) returns ‘i’
lower(‘I’, ‘en_us’) returns ‘i’
lower(‘I’, ‘tr_tr’) returns ‘ı’ (small dotless i)
If the second argument is omitted, NULL or empty text, you get the general case mapping for all languages.
SELECT UPPER(‘äüöß’)
This shows ÄÖÜSS as output.
Like
With an implementation to LIKE operator, you can now do like with unicode aware functions.
SELECT ‘Hällo’ LIKE ‘HÄ%’
This allows to match case insensitive with LIKE operator and handle unicode characters correctly.
RegExP
The REGEXP operator in SQLite allows you to use regular expressions in your query. First you specify the field or expression to search and then after the REGEXP keyword, you pass the regular expression pattern to match. This will return 1 if matches and 0 if not.
SELECT ‘Hällo’ REGEXP ‘H(.*)’
Loading Library
You can call LoadICU method in InternalSQLiteLibraryMBS module to load it explicitly, but otherwise we try to load it when SQLite initializes later. You can set ICUEnabled property to false in order to disable it explicitly.
On macOS or iOS we load the system ICU library.
On Windows, we try to load the DLL Xojo includes with your application.
On Linux we try to find the ICU libraries installed on the system.
Once loaded and enabled, we will pass the ICU functions to SQLite and you benefit from the functions. e.g. UPPER(‘ä’) will now return ‘Ä’ in a query.
We have a few flags to check and switch. ICULoad defines whether the libraries got loaded. ICUEnabled is your flag to enable/disable them, but we will keep that enabled by default. ICUUsed then tells you whether SQLite initialized and got the functions.
Please do not hesitate to contact us with questions. Coming soon in 21.2pr1.