Non-latin-characters ordering in database with "order by"

The concept of comparing and ordering the characters in a database is called collation.

How the strings are stored depends on the collation which is usually set in the server, client or session properties.

In MySQL:

SELECT  *FROM    (        SELECT  'a' AS str        UNION ALL        SELECT  'A' AS str        UNION ALL        SELECT  'b' AS str        UNION ALL        SELECT  'B' AS str        ) qORDER BY        str COLLATE UTF8_BIN--'A''B''a''b'

and

SELECT  *FROM    (        SELECT  'a' AS str        UNION ALL        SELECT  'A' AS str        UNION ALL        SELECT  'b' AS str        UNION ALL        SELECT  'B' AS str        ) qORDER BY        str COLLATE UTF8_GENERAL_CI--'a''A''b''B'

UTF8_BIN sorts characters according to their unicode. Caps have lower unicodes and therefore go first.

UTF8_GENERAL_CI sorts characters according to their alphabetical position, disregarding case.

Collation is also important for indexes, since the indexes rely heavily on sorting and comparison rules.

The important keyword in this case is 'collation'. I have no experience with SQLite, but would expect it to be similar to other database engines in that you can define the collation to use for whole databases, single tables, per connection, etc.

Check your DB documentation for the options available to you.

The ASCII codes for lower-case characters such as 'i' are greater than the ones for '[' and '_':

'i': 105'[': 91'_': 95

However, try to insert upper-case characters, eg. try with "IPOD" or "Iphone", those will become before "_" and "[" with the default binary collation.