Menu
just wondering if theres a way of sorting the results of a query ignoring case. Cause at the moment fields starting with an upper case letter are sorted and put on top of the list and fields starting with lower case letter are also sorted but are put after the upper case fields.
Thanks -- Mike
208
To be case insensitive on firstname , write this: select * from tbl where firstname='john' COLLATE NOCASE and lastname='doe' . It's specific to that one column, not the entire where clause.
Important Note: SQLite only understands upper/lower case for ASCII characters by default. The LIKE operator is case sensitive by default for unicode characters that are beyond the ASCII range.
SQL Server is, by default, case insensitive; however, it is possible to create a case-sensitive SQL Server database and even to make specific table columns case sensitive.
If you don't specify the ASC or DESC keyword, SQLite sorts the result set using the ASC option. In other words, it sorts the result set in the ascending order by default. In case you want to sort the result set by multiple columns, you use a comma (,) to separate two columns.
ORDER BY column COLLATE NOCASE;
See http://www.sqlite.org/datatype3.html#collation
142
On the Android you are given a collation function called LOCALIZED.
When you specify your column, do the following:
CREATE TABLE song(..., title TEXT COLLATE LOCALIZED ASC, ...)
It works great. If you have the Android source code, just do a search for "COLLATE" to see all sorts of examples.
21
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
