Documentation Source Text

<p>^A column that uses INTEGER affinity behaves the same as a column
with NUMERIC affinity.  The difference between INTEGER and NUMERIC affinity
is only evident in a [CAST expression].</p>

<p>^A column with REAL affinity behaves like a column with NUMERIC
affinity except that it forces integer values into floating point
representation.  (^As an internal optimization, small floating point
values with no fractional component are stored on
disk as integers in order to take up less space and are 
automatically converted back into floating point as the value is read out.

^This optimization is completely invisible at the SQL level and can only
be detected by examining the raw bits of the database file.)</p>

<p>^A column with affinity NONE does not prefer one storage class over
another and no attempt is made to coerce data from one storage class into
another.</p>

................................................................................
^(The expression "x IN (SELECT y ...)" is handled in the same way as the
expression "x = y" for the purposes of determining the collating sequence.)^
^(The collating sequence used for expressions of the form 
"x IN (y, z, ...)" is the collating sequence of x.)^
</p>  

<p>
^An ORDER BY clause that is part of a [SELECT]
statement may be assigned a collating sequence to be used for the sort
operation explicitly. ^In this case the explicit collating sequence is

always used.  ^Otherwise, if the expression sorted by an ORDER BY clause is
a column, then the collating sequence of the column is used to
determine sort order. ^If the expression is not a column, then the BINARY
collating sequence is used.
</p>  

<h3>6.2 Collation Sequence Examples</h3>
<p>
The examples below identify the collating sequences that would be used to
determine the results of text comparisons that may be performed by various
SQL statements. Note that a text comparison may not be required, and no
collating sequence used, in the case of numeric, blob or NULL values.
</p>
^(<blockquote>
<PRE>
CREATE TABLE t1(

    a,                 -- collating sequence BINARY
    b COLLATE BINARY,  -- collating sequence BINARY
    c COLLATE RTRIM,   -- collating sequence RTRIM
    d COLLATE NOCASE   -- collating sequence NOCASE
);






-- Text comparison is performed using the BINARY collating sequence.
SELECT a = b FROM t1;



-- Text comparison is performed using the RTRIM collating sequence.
SELECT a = b COLLATE RTRIM FROM t1;


-- Text comparison is performed using the NOCASE collating sequence.
SELECT d = a FROM t1;



-- Text comparison is performed using the BINARY collating sequence.
SELECT a = d FROM t1;



-- Text comparison is performed using the RTRIM collating sequence.
SELECT 'abc' = c FROM t1;



-- Text comparison is performed using the RTRIM collating sequence.
SELECT c = 'abc' FROM t1;



-- Grouping is performed using the NOCASE collating sequence (i.e. values
-- 'abc' and 'ABC' are placed in the same group).
SELECT count(*) GROUP BY d FROM t1;


-- Grouping is performed using the BINARY collating sequence.

SELECT count(*) GROUP BY (d || '') FROM t1;


-- Sorting is performed using the RTRIM collating sequence.
SELECT * FROM t1 ORDER BY c;


-- Sorting is performed using the BINARY collating sequence.
SELECT * FROM t1 ORDER BY (c || '');


-- Sorting is performed using the NOCASE collating sequence.
SELECT * FROM t1 ORDER BY c COLLATE NOCASE;

</PRE>


</blockquote>)^

<p>^A column that uses INTEGER affinity behaves the same as a column
with NUMERIC affinity.  The difference between INTEGER and NUMERIC affinity
is only evident in a [CAST expression].</p>

<p>^A column with REAL affinity behaves like a column with NUMERIC
affinity except that it forces integer values into floating point
representation.  (^As an internal optimization, small floating point
values with no fractional component and stored in columns with REAL
affinity are written to disk as integers in order to take up less 
space and are automatically converted back into floating point as
the value is read out.
^This optimization is completely invisible at the SQL level and can only
be detected by examining the raw bits of the database file.)</p>

<p>^A column with affinity NONE does not prefer one storage class over
another and no attempt is made to coerce data from one storage class into
another.</p>

................................................................................
^(The expression "x IN (SELECT y ...)" is handled in the same way as the
expression "x = y" for the purposes of determining the collating sequence.)^
^(The collating sequence used for expressions of the form 
"x IN (y, z, ...)" is the collating sequence of x.)^
</p>  

<p>
^Terms of the ORDER BY clause that is part of a [SELECT]
statement may be assigned a collating sequence using the 
[COLLATE operator], in which case the specified collating function is
used for sorting.
^Otherwise, if the expression sorted by an ORDER BY clause is
a column, then the collating sequence of the column is used to
determine sort order. ^If the expression is not a column and has no
COLLATE clause, then the BINARY collating sequence is used.
</p>  

<h3>6.2 Collation Sequence Examples</h3>
<p>
The examples below identify the collating sequences that would be used to
determine the results of text comparisons that may be performed by various
SQL statements. Note that a text comparison may not be required, and no
collating sequence used, in the case of numeric, blob or NULL values.
</p>
^(<blockquote>
<pre>
CREATE TABLE t1(
    x INTEGER PRIMARY KEY,
    a,                 /* collating sequence BINARY */
    b COLLATE BINARY,  /* collating sequence BINARY */
    c COLLATE RTRIM,   /* collating sequence RTRIM  */
    d COLLATE NOCASE   /* collating sequence NOCASE */
);
                   /* x   a     b     c       d */
INSERT INTO t1 VALUES(1,'abc','abc', 'abc  ','abc');
INSERT INTO t1 VALUES(2,'abc','abc', 'abc',  'ABC');
INSERT INTO t1 VALUES(3,'abc','abc', 'abc ', 'Abc');
INSERT INTO t1 VALUES(4,'abc','abc ','ABC',  'abc');
 
/* Text comparison a=b is performed using the BINARY collating sequence. */

SELECT x FROM t1 WHERE a = b ORDER BY x;
--result 1 2 3

/* Text comparison a=b is performed using the RTRIM collating sequence. */
SELECT x FROM t1 WHERE a = b COLLATE RTRIM ORDER BY x;
--result 1 2 3 4

/* Text comparison d=a is performed using the NOCASE collating sequence. */

SELECT x FROM t1 WHERE d = a ORDER BY x;
--result 1 2 3 4

/* Text comparison a=d is performed using the BINARY collating sequence. */

SELECT x FROM t1 WHERE a = d ORDER BY x;
--result 1 4

/* Text comparison 'abc'=c is performed using the RTRIM collating sequence. */

SELECT x FROM t1 WHERE 'abc' = c ORDER BY x;
--result 1 2 3

/* Text comparison c='abc' is performed using the RTRIM collating sequence. */

SELECT x FROM t1 WHERE c = 'abc' ORDER BY x;
--result 1 2 3

/* Grouping is performed using the NOCASE collating sequence (Values
** 'abc', 'ABC', and 'Abc' are placed in the same group). */
SELECT count(*) FROM t1 GROUP BY d ORDER BY 1;
--result 4

/* Grouping is performed using the BINARY collating sequence.  'abc' and
** 'ABC' and 'Abc' form different groups */
SELECT count(*) FROM t1 GROUP BY (d || '') ORDER BY 1;
--result 1 1 2

/* Sorting or column c is performed using the RTRIM collating sequence. */
SELECT x FROM t1 ORDER BY c, x;
--result 4 1 2 3

/* Sorting of (c||'') is performed using the BINARY collating sequence. */
SELECT x FROM t1 ORDER BY (c||''), x;
--result 4 2 3 1

/* Sorting of column c is performed using the NOCASE collating sequence. */
SELECT x FROM t1 ORDER BY c COLLATE NOCASE, x;


--result 2 4 3 1
</pre>
</blockquote>)^