Kalyani Phadke wrote: > but I am getting > Test > aaa > test > > Do I need to use COLLATE NOCASE while performing sorting on name column?
Yes, because the default COLLATE is memory based (just compares bytes). You get what you expect either by using "ORDER BY lower(Name)" (or upper, off course) or using "ORDER BY Name COLLATE NOCASE". But even so you only get ASCII order, that is, accented characters will not be ordered as you expect. You can fix this last issue by looking into the "ICU" extension. > Why? The reason is because SQLite can't guess what's the collation sequence you are interested. If you want to use the native system locale then your cased indexes could not be correct when used by another user (note that even some "standard" orderings may be different on different systems, although mostly on corner cases). The only way to really fix this issue is by having SQLite embed all possible collations into the library or use a 3rd party library that does this (the ICU extension does this, by relying on libicu). As the former would make SQLite not "lite" anymore, it's up to the user to either use the second or roll their own collating extensions. Note that it's easy enough to roll your own "icu" like extension (for example, if you only use Win32, you could just use the native system collation functions), but then you would need to make sure every access of your database would have available to this extensions. Regards, ~Nuno Lucas _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
