Documentation Source Text

Check-in [e357f599e3]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Update and clarify the documentation on datatypes and on CAST expressions.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: e357f599e319a2a8ae22f21cc7c11d8904ec18ec
User & Date: drh 2009-12-24 21:37:40
Context
2009-12-25
02:53
Additional refinements to the datatype3.html document. check-in: 4737512960 user: drh tags: trunk
2009-12-24
21:37
Update and clarify the documentation on datatypes and on CAST expressions. check-in: e357f599e3 user: drh tags: trunk
16:02
Clarifications to the datatype3.html document. check-in: 75e2505670 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/datatype3.in.

83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
...
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137


138
139
140
141
142
143
144
...
145
146
147
148
149
150
151

152
153
154
155
156
157
158
...
175
176
177
178
179
180
181




































































182
183



184

185
186

187
188
189


190
191
192
193

194


195
196
197

198
199





















200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
...
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246


247
248
249
250
251
252
253
...
283
284
285
286
287
288
289
290
291
292
293
294

295
296
297
298
299


300














301
302

303
304
305
306













307
308
309
310
311




312
313






314





315
316
317
318
319
320
321
</ul>

<p>Applications can chose to store dates and times in any of these
formats and freely convert between formats using the built-in date
and time functions.</p>


<tcl>hd_fragment affinity affinity</tcl>
<h2>2.0 Column Affinity</h2>

<p>
In order to maximize compatibility between SQLite and other database
engines, SQLite supports the concept of "type affinity" on columns.
The type affinity of a column is the recommended type for data stored
in that column.  The key here is that the type is recommended, not
required.  Any column can still store any type of data, in theory.
It is just that some columns, given the choice, will prefer to use
one storage class over another.  The preferred storage class for
a column is called its "affinity".
</p>

<p>^(Each column in an SQLite 3 database is assigned one of the
................................................................................

<p>^A column with TEXT affinity stores all data using storage classes
NULL, TEXT or BLOB. ^If numerical data is inserted into a column with
TEXT affinity it is converted into text form before being stored.</p>

<p>^A column with NUMERIC affinity may contain values using all five
storage classes.  ^When text data is inserted into a NUMERIC column, the
storage class of the text is converted to INTEGER or REAL if that conversion
is reversible - if the converversion does not lose information by truncating
the value.
^For conversions between TEXT and REAL storage classes, SQLite considers
the conversion to be lossless and reversible if the first 15 significant
digits of the number are preserved.
^If the lossless conversion of TEXT to INTEGER or REAL is not possible then
the value is stored using the TEXT storage class. ^No
attempt is made to convert NULL or BLOB values.</p>

<p>^A column that uses INTEGER affinity behaves in the same way as a
column with NUMERIC affinity, except that if a real value with no
fractional component and a magnitude that is less than or equal to the
largest possible integer (or text value that converts to such) is
inserted it is converted to an integer and stored using the INTEGER
storage class.  ^Note that a TEXT value inserted into a column with
INTEGER affinity will be stored as REAL if there is a lossless conversion
to REAL but not a lossless conversion to INTEGER.</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>


<h3>2.1 Determination Of Column Affinity</h3>

<p>^(The affinity of a column is determined by the declared type
of the column, according to the following rules in the order shown:)^</p>

<ol>
  <li><p>^If the datatype contains the string "INT" then it
................................................................................
</ol>

<p>^Note that the order of the rules for determining column affinity
is important.  ^A column whose declared datatype is "CHARINT" will match
both rules 1 and 2 but the first rule takes precedence and so the 
column affinity will be INTEGER.</p>





































































<h3>2.2 Column Affinity Example</h3>




^(<blockquote>

<PRE>CREATE TABLE t1(
    t  TEXT,

    nu NUMERIC, 
    i  INTEGER,
    no BLOB


);

-- Storage classes for the following row:
-- TEXT, REAL, INTEGER, TEXT

INSERT INTO t1 VALUES('500.0', '500.0', '500.0', '500.0');



-- Storage classes for the following row:
-- TEXT, REAL, INTEGER, REAL

INSERT INTO t1 VALUES(500.0, 500.0, 500.0, 500.0);
</PRE>





















</blockquote>)^

<tcl>hd_fragment comparisons {comparison expressions}</tcl>
<h2>3.0 Comparison Expressions</h2>

<p>SQLite version 3 has the usual set of SQL comparison operators
including "=", "&lt;", "&lt;=", "&gt;=", "!=", "IN", "BETWEEN", and "IS".

<h3>3.1 Sort Order</h3>

<p>^(The results of a comparison depend on the storage classes of the
operands, according to the following rules:)^</p>
<ul>
  <li><p>^A value with storage class NULL is considered less than any
  other value (including another value with storage class NULL).</p>

  <li><p>^An INTEGER or REAL value is less than any TEXT or BLOB value.
  ^When an INTEGER or REAL is compared to another INTEGER or REAL, a
  numerical comparison is performed.</p>
................................................................................
<tcl>hd_fragment expraff {expression affinity}</tcl>
<h3>3.2 Affinity Of Comparison Operands</h3>

<p>^SQLite may attempt to convert values between the storage classes
INTEGER, REAL, and/or TEXT before performing a comparison.
^Whether or not any conversions are attempted before the comparison takes
place depends on the affinity of the operands.
^(Affinity is assigned to expression operands by the the following rules:)^

<ul>
  <li><p>^An expression that is a simple reference to a column value
  has the same affinity as the column.
  ^(Note that if X and Y.Z 
  are column names, then +X and +Y.Z are considered expressions for the
  purpose of determining affinity.)^</p>

  <li><p>^(An expression of the form "CAST(&lt;expr&gt; TO &lt;type&gt;)"
  is assigned an affinity as if it were a reference to a column declared
  with type &lt;type&gt;)^


</ul>

<tcl>hd_fragment compaff {comparison affinity rules}</tcl>
<h3>3.3 Type Conversions Prior To Comparison</h3>

<p>Storage class conversions of operands prior to a binary comparison
operation occur according to the following rules in the order shown:</p>
................................................................................
a column, then the affinity of that column is used.  ^If the left operand
of IN is an expression then no storage class conversions occur.
</p>

<h3>3.4 Comparison Example</h3>

^(<blockquote>
<PRE>
CREATE TABLE t1(
    a TEXT,
    b NUMERIC,
    c BLOB

);

-- Storage classes for the following row:
-- TEXT, REAL, TEXT
INSERT INTO t1 VALUES('500', '500', '500');

















-- 60 and 40 are converted to '60' and '40' and values are compared as TEXT.
SELECT a &lt; 60, a &lt; 40 FROM t1;

1|0

-- Comparisons are numeric. No conversions are required.
SELECT b &lt; 60, b &lt; 600 FROM t1;













0|1

-- Both 60 and 600 (storage class NUMERIC) are less than '500'
-- (storage class TEXT).
SELECT c &lt; 60, c &lt; 600 FROM t1;




0|0
</PRE>






</blockquote>)^





<h2>4.0 Operators</h2>

<p>^(All mathematical operators (which is to say, all operators other
than the concatenation operator "||", LIKE, GLOB, MATCH, and REGEXP) 
apply NUMERIC affinity to both operands prior to being carried out.)^ 
^If one or both operands cannot be converted to NUMERIC then the result of the
mathematical operation is NULL.</p>







|
|





|







 







|
|
<


|




|
|
|
|
|
|
|
|
>
>







 







>







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|

>
>
>

>
|
<
>
|
|
<
>
>


<
<
>
|
>
>

<
|
>
|
<
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>










|
|







 







|









|
|
>
>







 







|

|
|
|
>


<
|
|
>
>

>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
<
>
|

<
<
>
>
>
>
>
>
>
>
>
>
>
>
>
|

<
<
<
>
>
>
>
|
<
>
>
>
>
>
>

>
>
>
>
>







83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
...
113
114
115
116
117
118
119
120
121

122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
...
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
...
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259

260
261
262

263
264
265
266


267
268
269
270
271

272
273
274

275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
...
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
...
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395

396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415

416
417
418


419
420
421
422
423
424
425
426
427
428
429
430
431
432
433



434
435
436
437
438

439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
</ul>

<p>Applications can chose to store dates and times in any of these
formats and freely convert between formats using the built-in date
and time functions.</p>


<tcl>hd_fragment affinity affinity {column affinity} {type affinity} {*affinities}</tcl>
<h2>2.0 Type Affinity</h2>

<p>
In order to maximize compatibility between SQLite and other database
engines, SQLite supports the concept of "type affinity" on columns.
The type affinity of a column is the recommended type for data stored
in that column.  The important ideay here is that the type is recommended, not
required.  Any column can still store any type of data, in theory.
It is just that some columns, given the choice, will prefer to use
one storage class over another.  The preferred storage class for
a column is called its "affinity".
</p>

<p>^(Each column in an SQLite 3 database is assigned one of the
................................................................................

<p>^A column with TEXT affinity stores all data using storage classes
NULL, TEXT or BLOB. ^If numerical data is inserted into a column with
TEXT affinity it is converted into text form before being stored.</p>

<p>^A column with NUMERIC affinity may contain values using all five
storage classes.  ^When text data is inserted into a NUMERIC column, the
storage class of the text is converted to INTEGER or REAL (in order of
preference) if such conversion is lossless and reversible.

^For conversions between TEXT and REAL storage classes, SQLite considers
the conversion to be lossless and reversible if the first 15 significant
decimal digits of the number are preserved.
^If the lossless conversion of TEXT to INTEGER or REAL is not possible then
the value is stored using the TEXT storage class. ^No
attempt is made to convert NULL or BLOB values.</p>

<p>^A string might look like a floating-point literal with
a decimal point and/or exponent notation but as long as
the value can be expressed as an integer, the NUMERIC affinity will convert
it into an integer. ^(Hence, the string '3.0e+5' is stored in a
column with NUMERIC affinity as the integer 30000, not as the floating
point value 30000.0.)^</p>

<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>

<tcl>hd_fragment affname {rules for determining column affinity}</tcl>
<h3>2.1 Determination Of Column Affinity</h3>

<p>^(The affinity of a column is determined by the declared type
of the column, according to the following rules in the order shown:)^</p>

<ol>
  <li><p>^If the datatype contains the string "INT" then it
................................................................................
</ol>

<p>^Note that the order of the rules for determining column affinity
is important.  ^A column whose declared datatype is "CHARINT" will match
both rules 1 and 2 but the first rule takes precedence and so the 
column affinity will be INTEGER.</p>

<h3>2.2 Affinity Name Examples</h3>

<p>The following table shows how many common data type names from
legacy SQL are converted into affinities by the five rules of the
previous section.  This table shows only a small subset of the
data type names that SQLite will accept.  Note that ^(numeric arguments
in parentheses that following the type name (ex: "VARCHAR(255)") are
ignored)^ by SQLite - SQLite does not impose any length restrictions
(other than the large global [SQLITE_MAX_LENGTH] limit) on the length of
string or BLOBs or numeric values.</p>

<blockquote>
<table border="1" cellpadding="5">
<tr><th>Example Typenames From The<br>CREATE TABLE Statement<br>
        or CAST Expression
    <th>Resulting Affinity
    <th>Rule Used To Determine Affinity

<tr><td align="center" valign="top">
  INT<br>
  INTEGER<br>
  TINYINT<br>
  SMALLINT<br>
  MEDIUMINT<br>
  BIGINT<br>
  UNSIGNED BIG INT<br>
  INT2<br>
  INT8
<td align="center">INTEGER
<td align="center">1

<tr><td align="center" valign="top">
  CHARACTER(20)<br>
  VARCHAR(255)<br>
  VARYING CHARACTER(255)<br>
  NCHAR(55)<br>
  NATIVE CHARACTER(70)<br>
  NVARCHAR(100)<br>
  TEXT<br>
  CLOB
<td align="center">TEXT
<td align="center">2

<tr><td align="center" valign="top">
  BLOB
<td align="center">NONE
<td align="center">3

<tr><td align="center" valign="top">
  REAL<br>
  DOUBLE<br>
  DOUBLE PRECISION<br>
  FLOAT<br>
  FLOATING POINT
<td align="center">REAL
<td align="center">4

<tr><td align="center" valign="top">
  NUMERIC<br>
  DECIMAL(10,5)<br>
  BOOLEAN<br>
  DATE<br>
  DATETIME
<td align="center">NUMERIC
<td align="center">5
</table>
</blockquote>>

<h3>2.3 Column Affinity Behavior Example</h3>

<p>The following SQL demonstrates how SQLite uses column affinity
to do type conversions when values are inserted into a table.</p>

^(<blockquote>
<pre>
CREATE TABLE t1(

    t  TEXT,     -- text affinity by rule 2
    nu NUMERIC,  -- numeric affinity by rule 5
    i  INTEGER,  -- integer affinity by rule 1

    r  REAL,     -- real affinity by rule 4
    no BLOB      -- no affinity by rule 3
);



-- Values stored as TEXT, INTEGER, INTEGER, REAL, TEXT.
INSERT INTO t1 VALUES('500.0', '500.0', '500.0', '500.0', '500.0');
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
text|integer|integer|real|text


-- Values stored as TEXT, INTEGER, INTEGER, REAL, REAL.
DELETE FROM t1;
INSERT INTO t1 VALUES(500.0, 500.0, 500.0, 500.0, 500.0);

SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
text|integer|integer|real|text

-- Values stored as TEXT, INTEGER, INTEGER, REAL, INTEGER.
DELETE FROM t1;
INSERT INTO t1 VALUES(500, 500, 500, 500, 500);
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
text|integer|integer|real|integer

-- BLOBs are always stored as BLOBs regardless of column affinity.
DELETE FROM t1;
INSERT INTO t1 VALUES(x'0500', x'0500', x'0500', x'0500', x'0500');
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
blob|blob|blob|blob|blob

-- NULLs are also unaffected by affinity
DELETE FROM t1;
INSERT INTO t1 VALUES(NULL,NULL,NULL,NULL,NULL);
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
null|null|null|null|null
</pre>
</blockquote>)^

<tcl>hd_fragment comparisons {comparison expressions}</tcl>
<h2>3.0 Comparison Expressions</h2>

<p>SQLite version 3 has the usual set of SQL comparison operators
including "=", "&lt;", "&lt;=", "&gt;=", "!=", "IN", "BETWEEN", and "IS".

<h3>3.1 Sort Order</h3>

<p>The results of a comparison depend on the storage classes of the
operands, according to the following rules:</p>
<ul>
  <li><p>^A value with storage class NULL is considered less than any
  other value (including another value with storage class NULL).</p>

  <li><p>^An INTEGER or REAL value is less than any TEXT or BLOB value.
  ^When an INTEGER or REAL is compared to another INTEGER or REAL, a
  numerical comparison is performed.</p>
................................................................................
<tcl>hd_fragment expraff {expression affinity}</tcl>
<h3>3.2 Affinity Of Comparison Operands</h3>

<p>^SQLite may attempt to convert values between the storage classes
INTEGER, REAL, and/or TEXT before performing a comparison.
^Whether or not any conversions are attempted before the comparison takes
place depends on the affinity of the operands.
Operand affinity is determined by the the following rules:

<ul>
  <li><p>^An expression that is a simple reference to a column value
  has the same affinity as the column.
  ^(Note that if X and Y.Z 
  are column names, then +X and +Y.Z are considered expressions for the
  purpose of determining affinity.)^</p>

  <li><p>^(An expression of the form "CAST(&lt;expr&gt; TO &lt;type&gt;)"
  has an affinity that is the same as a column with a declared
  type of &lt;type&gt;)^

  <li><p>^Otherwise, an expression has NONE affinity.
</ul>

<tcl>hd_fragment compaff {comparison affinity rules}</tcl>
<h3>3.3 Type Conversions Prior To Comparison</h3>

<p>Storage class conversions of operands prior to a binary comparison
operation occur according to the following rules in the order shown:</p>
................................................................................
a column, then the affinity of that column is used.  ^If the left operand
of IN is an expression then no storage class conversions occur.
</p>

<h3>3.4 Comparison Example</h3>

^(<blockquote>
<pre>
CREATE TABLE t1(
    a TEXT,      -- text affinity
    b NUMERIC,   -- numeric affinity
    c BLOB,      -- no affinity
    d            -- no affinity
);


-- Values will be stored as TEXT, INTEGER, TEXT, and INTEGER respectively
INSERT INTO t1 VALUES('500', '500', '500', 500);
SELECT typeof(a), typeof(b), typeof(c), typeof(d) FROM t1;
text|integer|text|integer

-- Because column "a" has text affinity, numeric values on the
-- right-hand side of the comparisons are converted to text before
-- the comparison occurs.
SELECT a &lt; 40,   a &lt; 60,   a &lt; 600 FROM t1;
0|1|1

-- Text affinity is applied to the right-hand operands but since
-- they are already TEXT this is a no-op; no conversions occur.
SELECT a &lt; '40', a &lt; '60', a &lt; '600' FROM t1;
0|1|1

-- Column "b" has numeric affinity and so numeric affinity is applied
-- to the operands on the right.  Since the operands are already numeric,
-- the application of affinity is a no-op; no conversions occur.  All
-- values are compared numerically.

SELECT b &lt; 40,   b &lt; 60,   b &lt; 600 FROM t1;
0|0|1



-- Numeric affinity is applied to operands no the right, converting them
-- from text to integers.  Then a numeric comparison occurs.
SELECT b &lt; '40', b &lt; '60', b &lt; '600' FROM t1;
0|0|1

-- No affinity conversions occur.  Right-hand side values all have
-- storage class INTEGER which are always less than the TEXT values
-- on the left.
SELECT c &lt; 40,   c &lt; 60,   c &lt; 600 FROM t1;
0|0|0

-- No affinity conversions occur.  Values are compared as TEXT.
SELECT c &lt; '40', c &lt; '60', c &lt; '600' FROM t1;
0|1|1




-- No affinity conversions occur.  Right-hand side values all have
-- storage class INTEGER which compare numerically with the INTEGER
-- values on the left.
SELECT d &lt; 40,   d &lt; 60,   d &lt; 600 FROM t1;
0|0|1


-- No affinity conversions occur.  INTEGER values on the left are
-- always less than TEXT values on the right.
SELECT d &lt; '40', d &lt; '60', d &lt; '600' FROM t1;
1|1|1
</pre>
</blockquote>)^

<p>^All of the result in the example are the same if the comparisons are
commuted - if expressions of the form "a&lt;40" are rewritten
as "40&gt;a".

<h2>4.0 Operators</h2>

<p>^(All mathematical operators (which is to say, all operators other
than the concatenation operator "||", LIKE, GLOB, MATCH, and REGEXP) 
apply NUMERIC affinity to both operands prior to being carried out.)^ 
^If one or both operands cannot be converted to NUMERIC then the result of the
mathematical operation is NULL.</p>

Changes to pages/lang.in.

1517
1518
1519
1520
1521
1522
1523

1524

1525
1526
1527
1528




















































1529
1530
1531
1532
1533
1534
1535

<p>^When a SELECT appears within an expression but is not the right
operand of an IN operator, then the first row of the result of the
SELECT becomes the value used in the expression.  ^If the SELECT yields
more than one result row, all rows after the first are ignored.  ^If
the SELECT yields no rows, then the value of the SELECT is NULL.</p>


<h3>CAST expressions</h3>

<p>^A CAST expression changes the [datatype] of the <expr> into the
type specified by &lt;type&gt;. 
^(&lt;type&gt; can be any non-empty type name that is valid
for the type in a column definition of a [CREATE TABLE] statement.)^</p>





















































<h3>Functions</h3>
<p>^Both [corefunc|simple] and [aggfunc|aggregate] functions are supported.
(For presentation purposes, simple functions are further subdivided into
[corefunc | core functions] and [datefunc | date-time functions].)
^A simple function can be used in any expression.  ^Simple functions return
a result immediately based on their inputs.  ^Aggregate functions







>

>



|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







1517
1518
1519
1520
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
1533
1534
1535
1536
1537
1538
1539
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
1550
1551
1552
1553
1554
1555
1556
1557
1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
1589

<p>^When a SELECT appears within an expression but is not the right
operand of an IN operator, then the first row of the result of the
SELECT becomes the value used in the expression.  ^If the SELECT yields
more than one result row, all rows after the first are ignored.  ^If
the SELECT yields no rows, then the value of the SELECT is NULL.</p>

<tcl>hd_fragment castexpr {CAST expression}</tcl>
<h3>CAST expressions</h3>

<p>^A CAST expression changes the [datatype] of the <expr> into the
type specified by &lt;type&gt;. 
^(&lt;type&gt; can be any non-empty type name that is valid
for the type in a column definition of a [CREATE TABLE] statement.)^
^The &lt;type&gt; name is interpreted according to the 
[rules for determining column affinity].

<p>^An explicit cast is strong than affinity; with the CAST expression
the datatype conversion is forced even if it is lossy and irrreversible.
<ul>
<li><p>
  ^A cast of a REAL value into an INTEGER will truncate the
  fractional part of the REAL.  ^If an REAL is too large to be
  represented as an INTEGER then the result of the cast is
  the largest negative integer: -9223372036854775808.

<li><p>
 ^A cast of a TEXT value into an INTEGER or REAL will read a prefix
 of the TEXT value that looks like an integer or real value, respectively,
 and ignore the rest.  ^When casting from TEXT into INTEGER or REAL,
 leading spaces in the TEXT value are ignored.  ^(A cast of a TEXT value
 that looks nothing like a number into INTEGER or REAL results in 0 or 0.0,
 respectively.)^

<li><p>
 ^Casting a value into NUMERIC first does a forced conversion into REAL
  but will then further converts the result into INTEGER if and only if
  the conversion from REAL to INTEGER is lossless and reversible.
  This is the only context in SQLite where the NUMERIC and INTEGER
  [affinities] behave differently.

<li><p>
 ^Casting a value into TEXT renders the value as if via [sqlite3_snprintf()]
  except that the resulting TEXT uses the [encoding] of the database
  connection.

<li><p>
 ^Casting a value to a &lt;type&gt; with no affinity causes the value to
  be converted into a BLOB.  ^Casting to a BLOB consists of first casting
  the value to TEXT in the [encoding] of the database connection, then
  interpreting the resulting byte sequence as a BLOB instead of as TEXT.

<li><p>
 ^Casting a BLOB value into any type other than a BLOB consists of first
  interpreting the byte sequence of the BLOB as a TEXT string in the
  database encoding then continuing as if the value where originally of
  type TEXT.

<li><p>
 ^Any cast of a NULL value yields a NULL result.
</ul>

<p>^Note that the result from casting any non-BLOB value into a 
BLOB and the result from casting any BLOB value into a non-BLOB value
will be different depending on whether the database [encoding] is UTF-8,
UTF-16be, or UTF-16le.

<h3>Functions</h3>
<p>^Both [corefunc|simple] and [aggfunc|aggregate] functions are supported.
(For presentation purposes, simple functions are further subdivided into
[corefunc | core functions] and [datefunc | date-time functions].)
^A simple function can be used in any expression.  ^Simple functions return
a result immediately based on their inputs.  ^Aggregate functions