SQLlite collate

Hello,
I’d appreciate an example how to use the SQLlite COLLATE operator.
For instance, where/how to insert it in the following sql? At the end of the string I tried adding COLLATE NOCASE or just NOCASE, but the code would not execute.
sqlr = “select vAuthor,vTitle,vVolume from kLibrary order by upper(vAuthor),vTitle,vVolume”

What I’m after is to have author “Šalamov” be printed before "T"s. At present I get:
Sinclair
Twain
Šalamov

while I would like to get
Sinclair
Šalamov
Twain

Thanks.

Official SQLite examples are here:

Looks like you’d do it like this:

sqlr = "SELECT vAuthor, vTitle, vVolume FROM kLibrary ORDER BY Upper(vAuthor) COLLATE NOCASE, vTitle, vVolume"

But I dont’ think that will provide the results desired (DEJA VU!)

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.

Meaning NOCASE affects ONLY “A” thru “Z”, of which “Š” is NOT a member and therefore would not be affected.

i think you can use MBS Sqlite extension to remove the accents. Am i right Christian??

RemoveAccentsCaseInsensitiveMBS(text as string) as string
RemoveAccentsMBS(text as string) as string

@ Paul [quote]sqlr = “SELECT vAuthor, vTitle, vVolume FROM kLibrary ORDER BY Upper(vAuthor) COLLATE NOCASE, vTitle, vVolume” [/quote]
Dave is right; I saw it, and among other variations I had already tried it, but with no result. As Dave predicted.

@Richard I do not own MBS.

BTW About listbox.sort: I find it interesting that the Finder puts “Š” before “T”, while a Xojo listbox.sort puts it after “Z”.

AFAIK the SQLite version that Xojo uses doesn’t support Unicode. Have you tried to use the extension from Thomas Tempelmann? See http://blog.tempel.org/2015/09/xojosqliteextensions.html

Downlading it. Thanks for the tip.

Unfortunately even with Thomas’ extension sorting doesn’t get as I would (“Š” before "T, etc.).
I wander how Xojo developers from countries where such characters or even accented letters like “É” are used, sort their listboxes.
Using asci-encoded strings to sort them would solve the problem only of few instances.

I’m looking to create a table in a Mysql db. How do I add this:
ENGINE = InnoDB CHARSET=utf8 COLLATE utf8_unicode_ci COMMENT = ‘Medics patient list’;

correctly to example sql string in Xojo:
sql = "CREATE TABLE Medic (id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY)…?