On 7 Sep 2011, at 9:39am, Sintoni Stefano (GMAIL) wrote:
> I need to perform one statement like the follow
>
> SELECT itemnames FROM table WHERE condition ORDER BY item ASC;
>
> But the ORDER BY need to be not case-sensitive.
sqlite> create table test (value text);
sqlite> insert into test values ('A');
sqlite> insert into test values ('b');
sqlite> insert into test values ('C');
sqlite> select * from test order by value;
A
C
b
sqlite> select * from test order by value collate nocase;
A
b
C
However, if case-sensitivity of 'item' never matters at all (and usually it
always matters or never matters) it is far better to build this information
into the design of your schema. You would do this as follows:
sqlite> create table testnocase (value text collate nocase);
sqlite> insert into testnocase values ('A');
sqlite> insert into testnocase values ('b');
sqlite> insert into testnocase values ('C');
sqlite> select * from testnocase order by value;
A
b
C
Also, hey presto:
sqlite> select * from test where value='B';
sqlite> select * from testnocase where value='B';
b
This means that all sorting and matching by that column will be done using
'NOCASE', so you don't have to remember to keep mentioning NOCASE or using LIKE
in every sort and match you do. It's faster too, since if you make an index on
that column, the index will be made in the basis of NOCASE too. When defining
a new table schema it's often worth considering NOCASE for any TEXT column you
might want to search or match on. Building this into the schema makes a good
explanation to anyone who has to work with your database.
Note that NOCASE does not correctly deal with the full range of Unicode
characters, just the Roman alphabet. Also, there's no 'COLLATE CASE' (if you
ever need to reverse it) you do 'COLLATE BINARY' instead.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
