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
