Hi Simon,
>>However, frequent use of COLLATE often suggests that the original schema
was badly chosen.
Any performance degradation?
Is it possible to change "collate nocase" to "collate binary" in schema for
already existing db? Any alternative solution for this?
Thanks.
On Tue, Sep 30, 2014 at 2:09 PM, Simon Slavin <[email protected]> wrote:
>
> On 30 Sep 2014, at 7:03am, dd <[email protected]> wrote:
>
> > Needs to support case-sensitive for case-insensitive sqlite
> > database field . To find exact case, COLLATE BINARY, works very well.
> >
> >
> >
> > What is the work around for like queries. Needs to case-sensitive for
> > like queries
>
> <http://www.sqlite.org/lang_expr.html>
>
> "The LIKE operator can be made case sensitive using the
> case_sensitive_like pragma."
>
> > and lessthan, greaterthan operators.
>
> <http://www.sqlite.org/lang_corefunc.html>
>
> "upper(X)", "lower(X)", but also see COLLATE operator as discussed below.
>
> > OR
> >
> > Is there anyway to change sqlite column's collating from NOCASE to
> BINARY?
>
> <http://www.sqlite.org/datatype3.html#collation>
>
> The three collations available are BINARY, NOCASE and RTRIM. (When NOCASE
> used with the higher Unicode characters you may have to think quite hard to
> figure out what's going on.)
>
> You can define collation for each column when you define a table. This is
> almost always all that's necessary: most of the time data in a database
> seems to have an inherent collation that suits it. You can define
> collation for each index element when you define an index (though you
> probably shouldn't: you should get it right when you define the table).
> You cannot change either of these without dropping and recreating the table
> or index.
>
> You can use a COLLATE operator for each operand in any expression. For
> instance ...
>
> SELECT x FROM t1 WHERE a = b COLLATE RTRIM ORDER BY x COLLATE RTRIM
>
> However, frequent use of COLLATE often suggests that the original schema
> was badly chosen. The only justified use of a lot of COLLATEs I ever saw
> was in a database which was designed to process a lot of text for someone
> who was doing research into words and how they are used.
>
> 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
