Perfect, more more thanks.
*Sintoni Stefano*
On 09/07/2011 11:37 AM, Simon Slavin wrote:
> 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
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
