Re: [sqlite] How to specify collating sequences in an expression.

[Michael Schlenker]

[EMAIL PROTECTED] schrieb:
SQLite has supported collating sequences since version 3.0.0.
A collating sequence is really a specification on how comparison
operators work on strings.
You can have arbitrary collating sequences in SQLite.  But for
the sake of this discussion, lets focus on just two:  BINARY
which is the default collating sequence, and NOCASE which does
case insensitive comparisons (on US-ASCII).

Consider this example table:

   CREATE TABLE t1(
     x TEXT COLLATE BINARY,
     y TEXT COLLATE NOCASE
   );
   INSERT INTO t1 VALUES('hello','Hello');

This query returns one row because comparisons against column y
ignore case:

   SELECT * FROM t1 WHERE y='HELLO';

This query returns no rows because comparisons against column x
take case into account.

   SELECT * FROM t1 WHERE x='HELLO';

When comparing two columns, the column on the left determines which
collating sequence to use.  In the first of the following two queries,
the column on the left uses NOCASE so one row is returned.  But in
the second query, the columns are reversed and the left-most column
uses BINARY.  As a result, no rows are returned from the second
query:

   SELECT * FROM t1 WHERE y=x;
   SELECT * FROM t1 WHERE x=y;

This last point seems a little goofy, but SQLite is documented as
working that way and the situation comes up so very rarely that nobody
has yet complained.

The problem with all of the above is that the collating sequence
on a column is specified when the column is declared and cannot
be changed.  What many people would like to have is some way to 
specify a different collating sequence to override the default
collating sequence for a single comparison.  The question is, what
should the syntax be.  Here are some ideas:

    SELECT * FROM t1 WHERE x='HELLO' COLLATE NOCASE;

Here the = operator has some added syntax at the end.  There some
parsing abiguities with this approach, but they can be dealt with
just as they are with the EXCEPT clause on a LIKE operator.  The
comparison operator syntax would be like this:

    <expr> <operator> <expr> [COLLATE <id>]

Another idea is to use CAST:

     SELECT * FROM t1 WHERE CAST(x AS TEXT COLLATE NOCASE)='HELLO';

Or perhaps

     SELECT * FROM t1 WHERE CASE(x COLLATE NOCASE)='HELLO';

A third idea is to invent entirely new syntax, perhaps like this:

     SELECT * FROM t1 WHERE COLLATE NOCASE(x='HELLO')

Please note that while I have used the = operator in all of the
examples above, everything applies equally to !=, <, <=, >, and >=.

Questions for SQLite community members:

  (1)  Is there some standard SQL syntax that I do not know about
       for doing this kind of thing?

  (2)  How do other SQL engines do this kind of thing?

Microsoft SQL Server does this either on the database level, by 
specifying the collation to use for the whole database, or at the column 
level with an alter table statement or during table creation.

See:
http://msdn2.microsoft.com/en-us/library/aa258237(SQL.80).aspx

Michael

-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------