In my SQLite 3 Database, I have some records with Turkish characters
such as "Ã", "Ã", "Ä°" etc. When I select my values withÂ
SELECT * FROM TABLE ORDER BY COLUMN_NAMEÂ
query, the records that begin with these characters are coming at the end.
Bare bone SQLite only collates correctly on the lower ASCII charset.
While that's fine for plain english, it doesn't work for most of us.
Normally, they should've come after the letter that is dot-less
version of each. Like "Ã" is after "O", "Ã" is after "U".
Is it something about regional settings? Is there a way to control
these settings?
You have the choice among some ways to get it right or close to right
for your language(s):
o) use ICU either as an extension (for third-party managers) or
linked to
your application.
Advantages: it works 100% correctly for a given language at a
time in each
operation.
Drawbacks: it's huge and slow and it requires you register a
collation for
every language you deal with. Also it won't work well for columns
containing several non-english languages.
o) write your own collation(s) invoking your OS' ICU routines to collate
strings.
Advantages: doesn't bloat your code with huge libraries.
Drawbacks: requires you write this extension (in C or something),
same
other drawbacks as ICU.
o) If you use Windows, download and use the functions in the extension I
wrote for a close-to-correct result.
Advantages: it's small, fairly fast and ready to use, it is language-
independant yet works decently well for many languages at
the same time; it also offers a number of Unicode-aware
string manipulation functions (unaccenting or not)
functions,
a fuzzy search function and much more. Comes as a C
source and
x86 DLL, free for any purpose.
Drawback: it probably doesn't work 100% correctly for any
language using
more than "vanilla english letters": your dotless i
will collate
along dotted i, for instance. It's a good compromise
between
absolute correctness for ONE language and "fair"
correctness for
most languages (including some asian languages using
diacritics)
Download: http://dl.dropbox.com/u/26433628/unifuzz.zip
I use SQLite Manager in Firefox to manage my DB.
My little extension will work with this one. You might also want to try
SQLite Expert which has ICU built-in (at least in its Pro version) and
_much_ more.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
