1
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
3
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
4
<title>Datatypes In SQLite Version 3</title>
5
<style type="text/css">
8
font-family: Verdana, sans-serif;
13
a:visited { color: #734559 }
15
.logo { position:absolute; margin:3px; }
31
.toolbar a { color: white; text-decoration: none; padding: 6px 12px; }
32
.toolbar a:visited { color: white; }
33
.toolbar a:hover { color: #044a64; background: white; }
35
.content { margin: 5%; }
36
.content dt { font-weight:bold; }
37
.content dd { margin-bottom: 25px; margin-left:20%; }
38
.content ul { padding:0px; padding-left: 15px; margin:0px; }
41
.se { background: url(images/se.gif) 100% 100% no-repeat #044a64}
42
.sw { background: url(images/sw.gif) 0% 100% no-repeat }
43
.ne { background: url(images/ne.gif) 100% 0% no-repeat }
44
.nw { background: url(images/nw.gif) 0% 0% no-repeat }
46
/* Things for "fancyformat" documents start here. */
47
.fancy img+p {font-style:italic}
48
.fancy .codeblock i { color: darkblue; }
49
.fancy h1,.fancy h2,.fancy h3,.fancy h4 {font-weight:normal;color:#044a64}
50
.fancy h2 { margin-left: 10px }
51
.fancy h3 { margin-left: 20px }
52
.fancy h4 { margin-left: 30px }
53
.fancy th {white-space:nowrap;text-align:left;border-bottom:solid 1px #444}
54
.fancy th, .fancy td {padding: 0.2em 1ex; vertical-align:top}
55
.fancy #toc a { color: darkblue ; text-decoration: none }
56
.fancy .todo { color: #AA3333 ; font-style : italic }
57
.fancy .todo:before { content: 'TODO:' }
58
.fancy p.todo { border: solid #AA3333 1px; padding: 1ex }
59
.fancy img { display:block; }
60
.fancy :link:hover, .fancy :visited:hover { background: wheat }
61
.fancy p,.fancy ul,.fancy ol { margin: 1em 5ex }
62
.fancy li p { margin: 1em 0 }
63
/* End of "fancyformat" specific rules. */
69
<div><!-- container div to satisfy validator -->
72
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite Logo"
74
<div><!-- IE hack to prevent disappearing logo--></div>
75
<div class="tagline">Small. Fast. Reliable.<br>Choose any three.</div>
77
<table width=100% style="clear:both"><tr><td>
78
<div class="se"><div class="sw"><div class="ne"><div class="nw">
79
<table width=100% style="padding:0;margin:0;cell-spacing:0"><tr>
82
<a href="about.html">About</a>
83
<a href="sitemap.html">Sitemap</a>
84
<a href="docs.html">Documentation</a>
85
<a href="download.html">Download</a>
86
<a href="copyright.html">License</a>
87
<a href="news.html">News</a>
88
<a href="support.html">Support</a>
91
gMsg = "Search SQLite Docs..."
92
function entersearch() {
93
var q = document.getElementById("q");
94
if( q.value == gMsg ) { q.value = "" }
95
q.style.color = "black"
96
q.style.fontStyle = "normal"
98
function leavesearch() {
99
var q = document.getElementById("q");
100
if( q.value == "" ) {
102
q.style.color = "#044a64"
103
q.style.fontStyle = "italic"
108
<div style="padding:0 1em 0px 0;white-space:nowrap">
109
<form name=f method="GET" action="http://www.sqlite.org/search">
110
<input id=q name=q type=text
111
onfocus="entersearch()" onblur="leavesearch()" style="width:24ex;padding:1px 1ex; border:solid white 1px; font-size:0.9em ; font-style:italic;color:#044a64;" value="Search SQLite Docs...">
112
<input type=submit value="Go" style="border:solid white 1px;background-color:#044a64;color:white;font-size:0.9em;padding:0 1ex">
116
</div></div></div></div>
118
<div class=startsearch></div>
123
<h1 align=center>Datatypes In SQLite Version 3</h1>
125
<p>Most SQL database engines (every SQL database engine other than SQLite,
126
as far as we know) uses static, rigid typing. With static typing, the datatype
127
of a value is determined by its container - the particular column in
128
which the value is stored.</p>
130
<p>SQLite uses a more general dynamic type system. In SQLite, the datatype
131
of a value is associated with the value itself, not with its container.
132
The dynamic type system of SQLite is backwards
133
compatible with the more common static type systems of other database engines
134
in the sense that SQL statement that work on statically typed databases should
135
work the same way in SQLite. However, the dynamic typing in SQLite allows
136
it to do things which are not possible in traditional rigidly typed
139
<a name="storageclasses"></a>
141
<h2>1.0 Storage Classes and Datatypes</h2>
143
<p>Each value stored in an SQLite database (or manipulated by the
144
database engine) has one of the following storage classes:</p>
147
The value is a NULL value.</p>
149
<li><p><B>INTEGER</B>. The value is a signed integer, stored in 1,
150
2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.</p>
152
<li><p><B>REAL</B>. The value is a floating point value, stored as
153
an 8-byte IEEE floating point number.</p>
155
<li><p><B>TEXT</B>. The value is a text string, stored using the
156
database encoding (UTF-8, UTF-16BE or UTF-16LE).</p>
158
<li><p><B>BLOB</B>. The value is a blob of data, stored exactly as
162
<p>Note that a storage class is slightly more general than a datatype.
163
The INTEGER storage class, for example, includes 6 different integer
164
datatypes of different lengths. This makes a difference on disk. But
165
as soon as INTEGER values are read off of disk and into memory for processing,
166
they are converted to the most general datatype (8-byte signed integer).
167
And so for the most part, "storage class" is indistinguishable from
168
"datatype" and the two terms can be used interchangeably.</p>
170
<p>Any column in an SQLite version 3 database,
171
except an <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a> column, may be used to store a value
172
of any storage class.</p>
174
<p>All values in SQL statements, whether they are literals embedded in SQL
175
statement text or <a href="lang_expr.html#varparam">parameters</a> bound to
176
<a href="c3ref/stmt.html">precompiled SQL statements</a>
177
have an implicit storage class.
178
Under circumstances described below, the
179
database engine may convert values between numeric storage classes
180
(INTEGER and REAL) and TEXT during query execution.
183
<a name="boolean"></a>
185
<h3>1.1 Boolean Datatype</h3>
187
<p>SQLite does not have a separate Boolean storage class.
188
Instead, Boolean values are stored as integers 0 (false) and 1 (true).</p>
190
<a name="datetime"></a>
192
<h3>1.2 Date and Time Datatype</h3>
194
<p>SQLite does not have a storage class set aside for storing
196
Instead, the built-in <a href="lang_datefunc.html">Date And Time Functions</a> of SQLite are capable of
197
storing dates and times as TEXT, REAL, or INTEGER values:</p>
200
<li><b>TEXT</b> as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
201
<li><b>REAL</b> as Julian day numbers, the number of days since
202
noon in Greenwich on November 24, 4714 B.C. according to the
203
proleptic Gregorian calendar.
204
<li><b>INTEGER</b> as Unix Time, the number of seconds since
205
1970-01-01 00:00:00 UTC.
208
<p>Applications can chose to store dates and times in any of these
209
formats and freely convert between formats using the built-in date
210
and time functions.</p>
213
<a name="affinity"></a>
215
<h2>2.0 Type Affinity</h2>
218
In order to maximize compatibility between SQLite and other database
219
engines, SQLite supports the concept of "type affinity" on columns.
220
The type affinity of a column is the recommended type for data stored
221
in that column. The important idea here is that the type is recommended, not
222
required. Any column can still store any type of data.
223
It is just that some columns, given the choice, will prefer to use
224
one storage class over another. The preferred storage class for
225
a column is called its "affinity".
228
<p>Each column in an SQLite 3 database is assigned one of the
229
following type affinities:</p>
238
<p>A column with TEXT affinity stores all data using storage classes
239
NULL, TEXT or BLOB. If numerical data is inserted into a column with
240
TEXT affinity it is converted into text form before being stored.</p>
242
<p>A column with NUMERIC affinity may contain values using all five
243
storage classes. When text data is inserted into a NUMERIC column, the
244
storage class of the text is converted to INTEGER or REAL (in order of
245
preference) if such conversion is lossless and reversible.
246
For conversions between TEXT and REAL storage classes, SQLite considers
247
the conversion to be lossless and reversible if the first 15 significant
248
decimal digits of the number are preserved.
249
If the lossless conversion of TEXT to INTEGER or REAL is not possible then
250
the value is stored using the TEXT storage class. No
251
attempt is made to convert NULL or BLOB values.</p>
253
<p>A string might look like a floating-point literal with
254
a decimal point and/or exponent notation but as long as
255
the value can be expressed as an integer, the NUMERIC affinity will convert
256
it into an integer. Hence, the string '3.0e+5' is stored in a
257
column with NUMERIC affinity as the integer 300000, not as the floating
258
point value 300000.0.</p>
260
<p>A column that uses INTEGER affinity behaves the same as a column
261
with NUMERIC affinity. The difference between INTEGER and NUMERIC affinity
262
is only evident in a <a href="lang_expr.html#castexpr">CAST expression</a>.</p>
264
<p>A column with REAL affinity behaves like a column with NUMERIC
265
affinity except that it forces integer values into floating point
266
representation. (As an internal optimization, small floating point
267
values with no fractional component and stored in columns with REAL
268
affinity are written to disk as integers in order to take up less
269
space and are automatically converted back into floating point as
270
the value is read out.
271
This optimization is completely invisible at the SQL level and can only
272
be detected by examining the raw bits of the database file.)</p>
274
<p>A column with affinity NONE does not prefer one storage class over
275
another and no attempt is made to coerce data from one storage class into
278
<a name="affname"></a>
280
<h3>2.1 Determination Of Column Affinity</h3>
282
<p>The affinity of a column is determined by the declared type
283
of the column, according to the following rules in the order shown:</p>
286
<li><p>If the declared type contains the string "INT" then it
287
is assigned INTEGER affinity.</p>
289
<li><p>If the declared type of the column contains any of the strings
290
"CHAR", "CLOB", or "TEXT" then that
291
column has TEXT affinity. Notice that the type VARCHAR contains the
292
string "CHAR" and is thus assigned TEXT affinity.</p>
294
<li><p>If the declared type for a column
295
contains the string "BLOB" or if
296
no type is specified then the column has affinity NONE.</p>
298
<li><p>If the declared type for a column
299
contains any of the strings "REAL", "FLOA",
300
or "DOUB" then the column has REAL affinity.</p>
302
<li><p>Otherwise, the affinity is NUMERIC.</p>
305
<p>Note that the order of the rules for determining column affinity
306
is important. A column whose declared type is "CHARINT" will match
307
both rules 1 and 2 but the first rule takes precedence and so the
308
column affinity will be INTEGER.</p>
310
<h3>2.2 Affinity Name Examples</h3>
312
<p>The following table shows how many common datatype names from
313
more traditional SQL implementations are converted into affinities by the five rules of the
314
previous section. This table shows only a small subset of the
315
datatype names that SQLite will accept. Note that numeric arguments
316
in parentheses that following the type name (ex: "VARCHAR(255)") are
317
ignored by SQLite - SQLite does not impose any length restrictions
318
(other than the large global <a href="limits.html#max_length">SQLITE_MAX_LENGTH</a> limit) on the length of
319
strings, BLOBs or numeric values.</p>
322
<table border="1" cellpadding="5">
323
<tr><th>Example Typenames From The<br>CREATE TABLE Statement<br>
325
<th>Resulting Affinity
326
<th>Rule Used To Determine Affinity
328
<tr><td align="center" valign="top">
338
<td align="center">INTEGER
341
<tr><td align="center" valign="top">
344
VARYING CHARACTER(255)<br>
346
NATIVE CHARACTER(70)<br>
350
<td align="center">TEXT
353
<tr><td align="center" valign="top">
355
<i>no datatype specified</i>
356
<td align="center">NONE
359
<tr><td align="center" valign="top">
364
<td align="center">REAL
367
<tr><td align="center" valign="top">
373
<td align="center">NUMERIC
378
<p>Note that a declared type of "FLOATING POINT" would give INTEGER
379
affinity, not REAL affinity, due to the "INT" at the end of "POINT".
380
And the declared type of "STRING" has an affinity of NUMERIC, not TEXT.
382
<h3>2.3 Column Affinity Behavior Example</h3>
384
<p>The following SQL demonstrates how SQLite uses column affinity
385
to do type conversions when values are inserted into a table.</p>
390
t TEXT, -- text affinity by rule 2
391
nu NUMERIC, -- numeric affinity by rule 5
392
i INTEGER, -- integer affinity by rule 1
393
r REAL, -- real affinity by rule 4
394
no BLOB -- no affinity by rule 3
397
-- Values stored as TEXT, INTEGER, INTEGER, REAL, TEXT.
398
INSERT INTO t1 VALUES('500.0', '500.0', '500.0', '500.0', '500.0');
399
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
400
text|integer|integer|real|text
402
-- Values stored as TEXT, INTEGER, INTEGER, REAL, REAL.
404
INSERT INTO t1 VALUES(500.0, 500.0, 500.0, 500.0, 500.0);
405
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
406
text|integer|integer|real|real
408
-- Values stored as TEXT, INTEGER, INTEGER, REAL, INTEGER.
410
INSERT INTO t1 VALUES(500, 500, 500, 500, 500);
411
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
412
text|integer|integer|real|integer
414
-- BLOBs are always stored as BLOBs regardless of column affinity.
416
INSERT INTO t1 VALUES(x'0500', x'0500', x'0500', x'0500', x'0500');
417
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
418
blob|blob|blob|blob|blob
420
-- NULLs are also unaffected by affinity
422
INSERT INTO t1 VALUES(NULL,NULL,NULL,NULL,NULL);
423
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
424
null|null|null|null|null
428
<a name="comparisons"></a>
430
<h2>3.0 Comparison Expressions</h2>
432
<p>SQLite version 3 has the usual set of SQL comparison operators
433
including "=", "==", "<", "<=", ">", ">=", "!=", "<>",
434
"IN", "NOT IN", "BETWEEN", "IS", and "IS NOT", .
436
<h3>3.1 Sort Order</h3>
438
<p>The results of a comparison depend on the storage classes of the
439
operands, according to the following rules:</p>
441
<li><p>A value with storage class NULL is considered less than any
442
other value (including another value with storage class NULL).</p>
444
<li><p>An INTEGER or REAL value is less than any TEXT or BLOB value.
445
When an INTEGER or REAL is compared to another INTEGER or REAL, a
446
numerical comparison is performed.</p>
448
<li><p>A TEXT value is less than a BLOB value. When two TEXT values
449
are compared an appropriate collating sequence is used to determine
452
<li><p>When two BLOB values are compared, the result is
453
determined using memcmp().</p>
456
<a name="expraff"></a>
458
<h3>3.2 Affinity Of Comparison Operands</h3>
460
<p>SQLite may attempt to convert values between the storage classes
461
INTEGER, REAL, and/or TEXT before performing a comparison.
462
Whether or not any conversions are attempted before the comparison takes
463
place depends on the affinity of the operands.
464
Operand affinity is determined by the following rules:
467
<li><p>An expression that is a simple reference to a column value
468
has the same affinity as the column.
469
Note that if X and Y.Z
470
are column names, then +X and +Y.Z are considered expressions for the
471
purpose of determining affinity.</p>
473
<li><p>An expression of the form "CAST(<i>expr</i> AS <i>type</i>)"
474
has an affinity that is the same as a column with a declared
475
type of "<i>type</i>".
477
<li><p>Otherwise, an expression has NONE affinity.
480
<a name="compaff"></a>
482
<h3>3.3 Type Conversions Prior To Comparison</h3>
484
<p>To "apply affinity" means to convert an operand to a particular storage
485
class if and only if the conversion is lossless and reversible.
486
Affinity is applied to operands of a comparison operator prior to
487
the comparison according to the following rules in the order shown:</p>
490
<li><p>If one operand has INTEGER, REAL or NUMERIC affinity
491
and the other operand as TEXT or NONE affinity
492
then NUMERIC affinity is applied to other operand.
494
<li><p>If one operand has TEXT affinity and the other has NONE affinity,
495
then TEXT affinity is applied to the other operand.
497
<li><p>Otherwise, no affinity is applied and both operands are compared
501
<p>The expression "a BETWEEN b AND c" is treated as two separate
502
binary comparisons "a >= b AND a <= c", even if that means
503
different affinities are applied to 'a' in each of the comparisons.
504
Datatype conversions in comparisons of the
505
form "x IN (SELECT y ...)" are handled is if
506
the comparison were really "x=y".
507
The expression "a IN (x, y, z, ...)" is equivalent to "a = +x OR
508
a = +y OR a = +z OR ...".
509
In other words, the values to the right of the IN operator (the "x", "y",
510
and "z" values in this example) are considered to have no affinity,
511
even if they happen to be column values or CAST expressions.
514
<h3>3.4 Comparison Example</h3>
519
a TEXT, -- text affinity
520
b NUMERIC, -- numeric affinity
521
c BLOB, -- no affinity
525
-- Values will be stored as TEXT, INTEGER, TEXT, and INTEGER respectively
526
INSERT INTO t1 VALUES('500', '500', '500', 500);
527
SELECT typeof(a), typeof(b), typeof(c), typeof(d) FROM t1;
528
text|integer|text|integer
530
-- Because column "a" has text affinity, numeric values on the
531
-- right-hand side of the comparisons are converted to text before
532
-- the comparison occurs.
533
SELECT a < 40, a < 60, a < 600 FROM t1;
536
-- Text affinity is applied to the right-hand operands but since
537
-- they are already TEXT this is a no-op; no conversions occur.
538
SELECT a < '40', a < '60', a < '600' FROM t1;
541
-- Column "b" has numeric affinity and so numeric affinity is applied
542
-- to the operands on the right. Since the operands are already numeric,
543
-- the application of affinity is a no-op; no conversions occur. All
544
-- values are compared numerically.
545
SELECT b < 40, b < 60, b < 600 FROM t1;
548
-- Numeric affinity is applied to operands on the right, converting them
549
-- from text to integers. Then a numeric comparison occurs.
550
SELECT b < '40', b < '60', b < '600' FROM t1;
553
-- No affinity conversions occur. Right-hand side values all have
554
-- storage class INTEGER which are always less than the TEXT values
556
SELECT c < 40, c < 60, c < 600 FROM t1;
559
-- No affinity conversions occur. Values are compared as TEXT.
560
SELECT c < '40', c < '60', c < '600' FROM t1;
563
-- No affinity conversions occur. Right-hand side values all have
564
-- storage class INTEGER which compare numerically with the INTEGER
565
-- values on the left.
566
SELECT d < 40, d < 60, d < 600 FROM t1;
569
-- No affinity conversions occur. INTEGER values on the left are
570
-- always less than TEXT values on the right.
571
SELECT d < '40', d < '60', d < '600' FROM t1;
576
<p>All of the result in the example are the same if the comparisons are
577
commuted - if expressions of the form "a<40" are rewritten
580
<h2>4.0 Operators</h2>
582
<p>All mathematical operators (+, -, *, /, %, <<, >>,
584
cast both operands to the NUMERIC storage class prior to being carried out.
585
The cast is carried through even if it is lossy and irreversible.
586
A NULL operand on a mathematical operator yields a NULL result.
587
An operand on a mathematical operator that does not look in any way
588
numeric and is not NULL is converted to 0 or 0.0.
591
<h2>5.0 Sorting, Grouping and Compound SELECTs</h2>
593
<p>When query results are sorted by an ORDER BY clause, values with storage
594
class NULL come first, followed by INTEGER and REAL values
595
interspersed in numeric order, followed by TEXT values in collating
596
sequence order, and finally BLOB values in memcmp() order. No storage
597
class conversions occur before the sort.</p>
599
<p>When grouping values with the GROUP BY clause values with
600
different storage classes are considered distinct, except for INTEGER
601
and REAL values which are considered equal if they are numerically
602
equal. No affinities are applied to any values as the result of a
605
<p>The compound SELECT operators UNION,
606
INTERSECT and EXCEPT perform implicit comparisons between values.
607
No affinity is applied to comparison operands for the implicit
608
comparisons associated with UNION, INTERSECT, or EXCEPT - the values
609
are compared as is.</p>
611
<a name="collation"></a>
613
<h2>6.0 Collating Sequences</h2>
615
<p>When SQLite compares two strings, it uses a collating sequence or
616
collating function (two words for the same thing) to determine which
617
string is greater or if the two strings are equal.
618
SQLite has three built-in collating functions: BINARY, NOCASE, and
622
<li><b>BINARY</b> - Compares string data using memcmp(), regardless
623
of text encoding.</li>
624
<li><b>NOCASE</b> - The same as binary, except the 26 upper case
625
characters of ASCII are folded to their lower case equivalents before
626
the comparison is performed. Note that only ASCII characters
627
are case folded. SQLite does not attempt to do full
628
UTF case folding due to the size of the tables required.</li>
630
<li><b>RTRIM</b> - The same as binary, except that trailing space
631
characters are ignored.</li>
634
<p>An application can register additional collating functions using
635
the <a href="c3ref/create_collation.html">sqlite3_create_collation()</a> interface.</p>
637
<h3>6.1 Assigning Collating Sequences from SQL</h3>
640
Every column of every
641
table has an associated collating function. If no collating function
642
is explicitly defined, then the collating function defaults to BINARY.
643
The COLLATE clause of the <a href="lang_createtable.html#tablecoldef">column definition</a> is used
644
to define alternative collating functions for a column.
648
The rules for determining which collating function to use for a
649
binary comparison operator (=, <, >, <=, >=, !=, IS, and
650
IS NOT) are as follows and in the order shown:
653
<li><p>If either operand has an explicit collating function assignment
654
using the postfix <a href="lang_expr.html#collateop">COLLATE operator</a>, then the explicit collating function
655
is used for comparison, with precedence to the collating function of the
656
left operand.</p></li>
658
<li><p>If either operand is a column, then the collating function of
659
that column is used with precedence to the left operand.
660
For the purposes of the previous sentence, a column name
661
preceded by one or more unary "+" operators is still considered a column name.
664
<li><p>Otherwise, the BINARY collating function is used for comparison.
669
An operand of a comparison is considered to have an explicit
670
collating function assignment (rule 1 above)
671
if any subexpression of the operand uses
672
the postfix <a href="lang_expr.html#collateop">COLLATE operator</a>. Thus, if a <a href="lang_expr.html#collateop">COLLATE operator</a> is used
673
anywhere in a comparision expression, the collating function defined
674
by that operator is used for string comparison regardless of what
675
table columns might be a part of that expression. If two or more
676
<a href="lang_expr.html#collateop">COLLATE operator</a> subexpressions appear anywhere in a comparison, the
677
left most explicit collating function is used regardless of how deeply the
678
COLLATE operators are nested in the expression and regardless of
679
how the expression is parenthesized.
683
The expression "x BETWEEN y and z" is logically
684
equivalent to two comparisons "x >= y AND x <= z" and works with
685
respect to collating functions as if it were two separate comparisons.
686
The expression "x IN (SELECT y ...)" is handled in the same way as the
687
expression "x = y" for the purposes of determining the collating sequence.
688
The collating sequence used for expressions of the form
689
"x IN (y, z, ...)" is the collating sequence of x.
693
Terms of the ORDER BY clause that is part of a <a href="lang_select.html">SELECT</a>
694
statement may be assigned a collating sequence using the
695
<a href="lang_expr.html#collateop">COLLATE operator</a>, in which case the specified collating function is
697
Otherwise, if the expression sorted by an ORDER BY clause is
698
a column, then the collating sequence of the column is used to
699
determine sort order. If the expression is not a column and has no
700
COLLATE clause, then the BINARY collating sequence is used.
703
<h3>6.2 Collation Sequence Examples</h3>
705
The examples below identify the collating sequences that would be used to
706
determine the results of text comparisons that may be performed by various
707
SQL statements. Note that a text comparison may not be required, and no
708
collating sequence used, in the case of numeric, blob or NULL values.
713
x INTEGER PRIMARY KEY,
714
a, /* collating sequence BINARY */
715
b COLLATE BINARY, /* collating sequence BINARY */
716
c COLLATE RTRIM, /* collating sequence RTRIM */
717
d COLLATE NOCASE /* collating sequence NOCASE */
720
INSERT INTO t1 VALUES(1,'abc','abc', 'abc ','abc');
721
INSERT INTO t1 VALUES(2,'abc','abc', 'abc', 'ABC');
722
INSERT INTO t1 VALUES(3,'abc','abc', 'abc ', 'Abc');
723
INSERT INTO t1 VALUES(4,'abc','abc ','ABC', 'abc');
725
/* Text comparison a=b is performed using the BINARY collating sequence. */
726
SELECT x FROM t1 WHERE a = b ORDER BY x;
729
/* Text comparison a=b is performed using the RTRIM collating sequence. */
730
SELECT x FROM t1 WHERE a = b COLLATE RTRIM ORDER BY x;
733
/* Text comparison d=a is performed using the NOCASE collating sequence. */
734
SELECT x FROM t1 WHERE d = a ORDER BY x;
737
/* Text comparison a=d is performed using the BINARY collating sequence. */
738
SELECT x FROM t1 WHERE a = d ORDER BY x;
741
/* Text comparison 'abc'=c is performed using the RTRIM collating sequence. */
742
SELECT x FROM t1 WHERE 'abc' = c ORDER BY x;
745
/* Text comparison c='abc' is performed using the RTRIM collating sequence. */
746
SELECT x FROM t1 WHERE c = 'abc' ORDER BY x;
749
/* Grouping is performed using the NOCASE collating sequence (Values
750
** 'abc', 'ABC', and 'Abc' are placed in the same group). */
751
SELECT count(*) FROM t1 GROUP BY d ORDER BY 1;
754
/* Grouping is performed using the BINARY collating sequence. 'abc' and
755
** 'ABC' and 'Abc' form different groups */
756
SELECT count(*) FROM t1 GROUP BY (d || '') ORDER BY 1;
759
/* Sorting or column c is performed using the RTRIM collating sequence. */
760
SELECT x FROM t1 ORDER BY c, x;
763
/* Sorting of (c||'') is performed using the BINARY collating sequence. */
764
SELECT x FROM t1 ORDER BY (c||''), x;
767
/* Sorting of column c is performed using the NOCASE collating sequence. */
768
SELECT x FROM t1 ORDER BY c COLLATE NOCASE, x;
added
removed
datatype3.html
