Documentation Source Text

Check-in [6c20f79cbc]
Login

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

Overview
Comment:Changes to CAST expression documentation to make for more testable statements.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 6c20f79cbcb04453f1d6f7f5cb82be0a9f277637
User & Date: dan 2010-08-26 19:07:32
Context
2010-08-27
11:20
Minor fixes to CAST documentation. check-in: 302d0bb876 user: dan tags: trunk
2010-08-26
19:07
Changes to CAST expression documentation to make for more testable statements. check-in: 6c20f79cbc user: dan tags: trunk
2010-08-24
13:11
Modifications to documentation for CASE expressions. check-in: 6c47d70dc0 user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/datatype3.in.

13
14
15
16
17
18
19

20
21
22
23
24
25
26
The dynamic type system of SQLite is backwards
compatible with the more common static type systems of other database engines
in the sense that SQL statement that work on statically typed databases should
work the same way in SQLite.  However, the dynamic typing in SQLite allows
it to do things which are not possible in traditional rigidly typed
databases.</p>


<h2>1.0 Storage Classes and Datatypes</h2>

<p>Each value stored in an SQLite database (or manipulated by the
database engine) has one of the following storage classes:</p>
<ul>
  <li><p><B>NULL</B>.
  The value is a NULL value.</p>







>







13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
The dynamic type system of SQLite is backwards
compatible with the more common static type systems of other database engines
in the sense that SQL statement that work on statically typed databases should
work the same way in SQLite.  However, the dynamic typing in SQLite allows
it to do things which are not possible in traditional rigidly typed
databases.</p>

<tcl>hd_fragment storageclasses {storage class}</tcl>
<h2>1.0 Storage Classes and Datatypes</h2>

<p>Each value stored in an SQLite database (or manipulated by the
database engine) has one of the following storage classes:</p>
<ul>
  <li><p><B>NULL</B>.
  The value is a NULL value.</p>

Changes to pages/lang.in.

1604
1605
1606
1607
1608
1609
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619
1620
1621
1622
1623
1624
1625
1626




1627
1628
1629
1630
1631
1632
1633
1634

1635
1636
1637
1638
1639
1640
1641
1642
1643
1644
1645
1646




1647
1648
1649
1650
1651

1652
1653
1654
1655
1656
1657






1658
1659
1660












1661


1662
1663
1664
1665
1666
1667
1668
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 stronger 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>







|
|
|
|
|
|

|
|
|
|
<
<
<
<

>
>
>
>
|
|
|
|
|
|
<

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

>
>
>
>
|
|
|
|
|
>

<
|
|
|
|
>
>
>
>
>
>

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







1604
1605
1606
1607
1608
1609
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619
1620
1621




1622
1623
1624
1625
1626
1627
1628
1629
1630
1631
1632

1633
1634
1635
1636
1637




1638
1639
1640
1641
1642
1643
1644
1645
1646
1647
1648
1649
1650
1651
1652
1653

1654
1655
1656
1657
1658
1659
1660
1661
1662
1663
1664


1665
1666
1667
1668
1669
1670
1671
1672
1673
1674
1675
1676
1677
1678
1679
1680
1681
1682
1683
1684
1685
1686
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 is used to convert the value of &lt;expr&gt; to 
a different [storage class] in a similar way to the conversion that takes
place when a [column affinity] is applied to a value. ^Application of a CAST
expression is different to application of a column affinity, as
with a CAST expression the storage class conversion is forced even 
if it is lossy and irrreversible.

<p>^If the value of &lt;expr&gt; is NULL, then the result of the CAST
expression is also NULL. ^Otherwise, the storage class of the result value
is determined by applying the [rules for determining column affinity] to
the &lt;type-name&gt; specified as part of the CAST expression.





<table border=1>
<tr>
  <th> Affinity of<br>&lt;type-name&gt;
  <th> Conversion Processing
<tr>
  <td> NONE 
  <td> ^Casting a value to a &lt;type-name&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.


<tr>
  <td> TEXT
  <td> ^To cast a BLOB value to TEXT, the sequence of bytes that make up the
  BLOB is interpreted as text encoded using the database encoding.




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

<tr>
  <td> REAL
  <td> ^When casting a BLOB value to a REAL, the value is first converted to
        TEXT.
       <p>^When casting a TEXT value to REAL, the longest possible prefix of
        the value that can be interpreted as a real number is extracted from
        the TEXT value and the remainder ignored. ^Any leading spaces in the
        TEXT value are ignored when converging from TEXT to REAL. ^If there is
        no prefix that can be interpreted as a real number, the result of the
        conversion is 0.0.


<tr>
  <td> INTEGER
  <td> ^When casting a BLOB value to INTEGER, the value is first converted to
        TEXT.
       <p>^When casting a TEXT value to INTEGER, the longest possible prefix of
        the value that can be interpreted as an integer number is extracted from
        the TEXT value and the remainder ignored. ^Any leading spaces in the
        TEXT value when converting from TEXT to INTEGER are ignored. ^If there
        is no prefix that can be interpreted as an integer number, the result
        of the conversion is 0.



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

<tr>
  <td> NUMERIC
  <td> ^Casting a value into NUMERIC first does a forced conversion into REAL
  but 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.

</tr>

</table>

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