Debian Codegraph @

www/datatype3.html

raw
1 <!DOCTYPE html>
2 <html><head>
3 <meta name="viewport" content="width=device-width, initial-scale=1.0">
4 <meta http-equiv="content-type" content="text/html; charset=UTF-8">
5 <link href="sqlite.css" rel="stylesheet">
6 <title>Datatypes In SQLite</title>
7 <!-- path= -->
8 </head>
9 <body>
10 <div class=nosearch>
11 <a href="index.html">
12 <img class="logo" src="images/sqlite370_banner.svg" alt="SQLite" border="0">
13 </a>
14 <div><!-- IE hack to prevent disappearing logo --></div>
15 <div class="tagline desktoponly">
16 Small. Fast. Reliable.<br>Choose any three.
17 </div>
18 <div class="menu mainmenu">
19 <ul>
20 <li><a href="index.html">Home</a>
21 <li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
22 <li class='wideonly'><a href='about.html'>About</a>
23 <li class='desktoponly'><a href="docs.html">Documentation</a>
24 <li class='desktoponly'><a href="download.html">Download</a>
25 <li class='wideonly'><a href='copyright.html'>License</a>
26 <li class='desktoponly'><a href="support.html">Support</a>
27 <li class='desktoponly'><a href="prosupport.html">Purchase</a>
28 <li class='search' id='search_menubutton'>
29 <a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
30 </ul>
31 </div>
32 <div class="menu submenu" id="submenu">
33 <ul>
34 <li><a href='about.html'>About</a>
35 <li><a href='docs.html'>Documentation</a>
36 <li><a href='download.html'>Download</a>
37 <li><a href='support.html'>Support</a>
38 <li><a href='prosupport.html'>Purchase</a>
39 </ul>
40 </div>
41 <div class="searchmenu" id="searchmenu">
42 <form method="GET" action="search">
43 <select name="s" id="searchtype">
44 <option value="d">Search Documentation</option>
45 <option value="c">Search Changelog</option>
46 </select>
47 <input type="text" name="q" id="searchbox" value="">
48 <input type="submit" value="Go">
49 </form>
50 </div>
51 </div>
52 <script>
53 function toggle_div(nm) {
54 var w = document.getElementById(nm);
55 if( w.style.display=="block" ){
56 w.style.display = "none";
57 }else{
58 w.style.display = "block";
59 }
60 }
61 function toggle_search() {
62 var w = document.getElementById("searchmenu");
63 if( w.style.display=="block" ){
64 w.style.display = "none";
65 } else {
66 w.style.display = "block";
67 setTimeout(function(){
68 document.getElementById("searchbox").focus()
69 }, 30);
70 }
71 }
72 function div_off(nm){document.getElementById(nm).style.display="none";}
73 window.onbeforeunload = function(e){div_off("submenu");}
74 /* Disable the Search feature if we are not operating from CGI, since */
75 /* Search is accomplished using CGI and will not work without it. */
76 if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
77 document.getElementById("search_menubutton").style.display = "none";
78 }
79 /* Used by the Hide/Show button beside syntax diagrams, to toggle the */
80 function hideorshow(btn,obj){
81 var x = document.getElementById(obj);
82 var b = document.getElementById(btn);
83 if( x.style.display!='none' ){
84 x.style.display = 'none';
85 b.innerHTML='show';
86 }else{
87 x.style.display = '';
88 b.innerHTML='hide';
89 }
90 return false;
91 }
92 var antiRobot = 0;
93 function antiRobotGo(){
94 if( antiRobot!=3 ) return;
95 antiRobot = 7;
96 var j = document.getElementById("mtimelink");
97 if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
98 }
99 function antiRobotDefense(){
100 document.body.onmousedown=function(){
101 antiRobot |= 2;
102 antiRobotGo();
103 document.body.onmousedown=null;
104 }
105 document.body.onmousemove=function(){
106 antiRobot |= 2;
107 antiRobotGo();
108 document.body.onmousemove=null;
109 }
110 setTimeout(function(){
111 antiRobot |= 1;
112 antiRobotGo();
113 }, 100)
114 antiRobotGo();
115 }
116 antiRobotDefense();
117 </script>
118 <div class=fancy>
119 <div class=nosearch>
120 <div class="fancy_title">
121 Datatypes In SQLite
122 </div>
123 <details class="fancy_toc">
124 <summary>Table Of Contents</summary>
125 <div id="toc_sub"><div class="fancy-toc1"><a href="#datatypes_in_sqlite">1. Datatypes In SQLite</a></div>
126 <div class="fancy-toc1"><a href="#storage_classes_and_datatypes">2. Storage Classes and Datatypes</a></div>
127 <div class="fancy-toc2"><a href="#boolean_datatype">2.1. Boolean Datatype</a></div>
128 <div class="fancy-toc2"><a href="#date_and_time_datatype">2.2. Date and Time Datatype</a></div>
129 <div class="fancy-toc1"><a href="#type_affinity">3. Type Affinity</a></div>
130 <div class="fancy-toc2"><a href="#determination_of_column_affinity">3.1. Determination Of Column Affinity</a></div>
131 <div class="fancy-toc3"><a href="#affinity_name_examples">3.1.1. Affinity Name Examples</a></div>
132 <div class="fancy-toc2"><a href="#affinity_of_expressions">3.2. Affinity Of Expressions</a></div>
133 <div class="fancy-toc2"><a href="#column_affinity_for_views_and_subqueries">3.3. Column Affinity For Views And Subqueries</a></div>
134 <div class="fancy-toc3"><a href="#column_affinity_for_compound_views">3.3.1. Column Affinity For Compound Views</a></div>
135 <div class="fancy-toc2"><a href="#column_affinity_behavior_example">3.4. Column Affinity Behavior Example</a></div>
136 <div class="fancy-toc1"><a href="#comparison_expressions">4. Comparison Expressions</a></div>
137 <div class="fancy-toc2"><a href="#sort_order">4.1. Sort Order</a></div>
138 <div class="fancy-toc2"><a href="#type_conversions_prior_to_comparison">4.2. Type Conversions Prior To Comparison</a></div>
139 <div class="fancy-toc2"><a href="#comparison_example">4.3. Comparison Example</a></div>
140 <div class="fancy-toc1"><a href="#operators">5. Operators</a></div>
141 <div class="fancy-toc1"><a href="#sorting_grouping_and_compound_selects">6. Sorting, Grouping and Compound SELECTs</a></div>
142 <div class="fancy-toc1"><a href="#collating_sequences">7. Collating Sequences</a></div>
143 <div class="fancy-toc2"><a href="#assigning_collating_sequences_from_sql">7.1. Assigning Collating Sequences from SQL</a></div>
144 <div class="fancy-toc2"><a href="#collation_sequence_examples">7.2. Collation Sequence Examples</a></div>
145 </div>
146 </details>
147 </div>
148
149
150
151
152
153 <h1 id="datatypes_in_sqlite"><span>1. </span>Datatypes In SQLite</h1>
154 <p>Most SQL database engines (every SQL database engine other than SQLite,
155 as far as we know) uses static, rigid typing. With static typing, the datatype
156 of a value is determined by its container - the particular column in
157 which the value is stored.</p>
158
159 <p>SQLite uses a more general dynamic type system. In SQLite, the datatype
160 of a value is associated with the value itself, not with its container.
161 The dynamic type system of SQLite is backwards
162 compatible with the more common static type systems of other database engines
163 in the sense that SQL statements that work on statically typed databases
164 work the same way in SQLite. However, the dynamic typing in SQLite allows
165 it to do things which are not possible in traditional rigidly typed
166 databases. <a href="flextypegood.html">Flexible typing is a feature</a> of SQLite, not a bug.</p>
167
168 <p>Update:
169 As of version 3.37.0 (2021-11-27), SQLite provides <a href="stricttables.html">STRICT tables</a>
170 that do rigid type enforcement, for developers who prefer that kind of thing.
171
172 <a name="storageclasses"></a>
173
174 </p><h1 id="storage_classes_and_datatypes"><span>2. </span>Storage Classes and Datatypes</h1>
175
176 <p>Each value stored in an SQLite database (or manipulated by the
177 database engine) has one of the following storage classes:</p>
178 <ul>
179 <li><p><b>NULL</b>.
180 The value is a NULL value.</p>
181
182 </li><li><p><b>INTEGER</b>. The value is a signed integer, stored in 0, 1,
183 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.</p>
184
185 </li><li><p><b>REAL</b>. The value is a floating point value, stored as
186 an 8-byte IEEE floating point number.</p>
187
188 </li><li><p><b>TEXT</b>. The value is a text string, stored using the
189 database encoding (UTF-8, UTF-16BE or UTF-16LE).</p>
190
191 </li><li><p><b>BLOB</b>. The value is a blob of data, stored exactly as
192 it was input.</p>
193 </li></ul>
194
195 <p>A storage class is more general than a datatype.
196 The INTEGER storage class, for example, includes 7 different integer
197 datatypes of different lengths.
198 <a href="fileformat2.html#record_format">This makes a difference on disk.</a>
199 But as soon as INTEGER values are read off of disk and into memory for
200 processing, they are converted to the most general datatype
201 (8-byte signed integer).
202 And so for the most part, "storage class" is indistinguishable from
203 "datatype" and the two terms can be used interchangeably.</p>
204
205 <p>Any column in an SQLite version 3 database,
206 except an <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a> column, may be used to store a value
207 of any storage class.</p>
208
209 <p>All values in SQL statements, whether they are literals embedded in SQL
210 statement text or <a href="lang_expr.html#varparam">parameters</a> bound to
211 <a href="c3ref/stmt.html">precompiled SQL statements</a>
212 have an implicit storage class.
213 Under circumstances described below, the
214 database engine may convert values between numeric storage classes
215 (INTEGER and REAL) and TEXT during query execution.
216 </p>
217
218 <a name="boolean"></a>
219
220 <h2 id="boolean_datatype"><span>2.1. </span>Boolean Datatype</h2>
221
222 <p>SQLite does not have a separate Boolean storage class.
223 Instead, Boolean values are stored as integers 0 (false) and 1 (true).</p>
224
225 <p>SQLite recognizes the keywords "TRUE" and "FALSE",
226 as of version 3.23.0 (2018-04-02) but those keywords are
227 really just alternative spellings for the integer literals 1 and 0
228 respectively.
229
230 <a name="datetime"></a>
231
232 </p><h2 id="date_and_time_datatype"><span>2.2. </span>Date and Time Datatype</h2>
233
234 <p>SQLite does not have a storage class set aside for storing
235 dates and/or times.
236 Instead, the built-in <a href="lang_datefunc.html">Date And Time Functions</a> of SQLite are capable of
237 storing dates and times as TEXT, REAL, or INTEGER values:</p>
238
239 <ul>
240 <li><b>TEXT</b> as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
241 </li><li><b>REAL</b> as Julian day numbers, the number of days since
242 noon in Greenwich on November 24, 4714 B.C. according to the
243 proleptic Gregorian calendar.
244 </li><li><b>INTEGER</b> as Unix Time, the number of seconds since
245 1970-01-01 00:00:00 UTC.
246 </li></ul>
247
248 <p>Applications can choose to store dates and times in any of these
249 formats and freely convert between formats using the built-in
250 <a href="lang_datefunc.html">date and time functions</a>.</p>
251
252
253 <a name="affinity"></a>
254
255 <h1 id="type_affinity"><span>3. </span>Type Affinity</h1>
256
257 <p>
258 SQL database engines that use rigid typing will usually try to
259 automatically convert values to the appropriate datatype. Consider this:
260
261 </p><blockquote><pre>
262 CREATE TABLE t1(a INT, b VARCHAR(10));
263 INSERT INTO t1(a,b) VALUES('123',456);
264 </pre></blockquote>
265
266 <p>
267 Rigidly-typed database will convert the string '123' into an
268 integer 123 and the integer 456 into a string '456' prior to
269 doing the insert.
270
271 </p><p>
272 In order to maximize compatibility between SQLite and other database
273 engines, and so that the example above will work on SQLite as it does
274 on other SQL database engines,
275 SQLite supports the concept of "type affinity" on columns.
276 The type affinity of a column is the recommended type for data stored
277 in that column. The important idea here is that the type is recommended, not
278 required. Any column can still store any type of data.
279 It is just that some columns, given the choice, will prefer to use
280 one storage class over another. The preferred storage class for
281 a column is called its "affinity".
282 </p>
283
284 <p>Each column in an SQLite 3 database is assigned one of the
285 following type affinities:</p>
286 <ul>
287 <li>TEXT</li>
288 <li>NUMERIC</li>
289 <li>INTEGER</li>
290 <li>REAL</li>
291 <li>BLOB</li>
292 </ul>
293
294 <p>(Historical note: The "BLOB" type affinity used to be called "NONE".
295 But that term was easy to confuse with "no affinity" and so it was
296 renamed.)
297
298 </p><p>A column with TEXT affinity stores all data using storage classes
299 NULL, TEXT or BLOB. If numerical data is inserted into a column with
300 TEXT affinity it is converted into text form before being stored.</p>
301
302 <p>A column with NUMERIC affinity may contain values using all five
303 storage classes. When text data is inserted into a NUMERIC column, the
304 storage class of the text is converted to INTEGER or REAL (in order of
305 preference) if the text is a well-formed integer or real literal, respectively.
306 If the TEXT value is a well-formed integer literal that is too large
307 to fit in a 64-bit signed integer, it is converted to REAL.
308 For conversions between TEXT and REAL storage classes, about
309 15.95 significant decimal digits of the number are preserved.
310 (The conversion accuracy is limited by the use of IEEE 754 binary64
311 or "double" encoding for <a href="floatingpoint.html">floating-point values</a>.)
312 If the TEXT value is not a well-formed integer or real literal,
313 then the value is stored as TEXT.
314 For the purposes of this paragraph, hexadecimal integer
315 literals are not considered well-formed and are stored as TEXT.
316 (This is done for historical compatibility with versions of SQLite
317 prior to <a href="releaselog/3_8_6.html">version 3.8.6</a> 2014-08-15 where hexadecimal integer
318 literals were first introduced into SQLite.)
319 If a floating point value that can be represented exactly as an integer
320 is inserted into a column with NUMERIC affinity, the value is
321 converted into an integer.
322 No attempt is made to convert NULL or BLOB values.</p>
323
324 <p>A string might look like a floating-point literal with
325 a decimal point and/or exponent notation but as long as
326 the value can be expressed as an integer, the NUMERIC affinity will convert
327 it into an integer. Hence, the string '3.0e+5' is stored in a
328 column with NUMERIC affinity as the integer 300000, not as the floating
329 point value 300000.0.</p>
330
331 <p>A column that uses INTEGER affinity behaves the same as a column
332 with NUMERIC affinity. The difference between INTEGER and NUMERIC affinity
333 is only evident in a <a href="lang_expr.html#castexpr">CAST expression</a>: The expression
334 "CAST(4.0 AS INT)" returns an integer 4, whereas
335 "CAST(4.0 AS NUMERIC)" leaves the value as a floating-point 4.0.</p>
336
337 <p>A column with REAL affinity behaves like a column with NUMERIC
338 affinity except that it forces integer values into floating point
339 representation. (As an internal optimization, small floating point
340 values with no fractional component and stored in columns with REAL
341 affinity are written to disk as integers in order to take up less
342 space and are automatically converted back into floating point as
343 the value is read out.
344 This optimization is completely invisible at the SQL level and can only
345 be detected by examining the raw bits of the database file.)</p>
346
347 <p>A column with affinity BLOB does not prefer one storage class over
348 another and no attempt is made to coerce data from one storage class into
349 another.</p>
350
351 <a name="affname"></a>
352
353 <h2 id="determination_of_column_affinity"><span>3.1. </span>Determination Of Column Affinity</h2>
354
355 <p>For tables not declared as <a href="stricttables.html">STRICT</a>,
356 the affinity of a column is determined by the declared type
357 of the column, according to the following rules in the order shown:</p>
358
359 <ol>
360 <li><p>If the declared type contains the string "INT" then it
361 is assigned INTEGER affinity.</p>
362
363 </li><li><p>If the declared type of the column contains any of the strings
364 "CHAR", "CLOB", or "TEXT" then that
365 column has TEXT affinity. Notice that the type VARCHAR contains the
366 string "CHAR" and is thus assigned TEXT affinity.</p>
367
368 </li><li><p>If the declared type for a column
369 contains the string "BLOB" or if
370 no type is specified then the column has affinity BLOB.</p>
371
372 </li><li><p>If the declared type for a column
373 contains any of the strings "REAL", "FLOA",
374 or "DOUB" then the column has REAL affinity.</p>
375
376 </li><li><p>Otherwise, the affinity is NUMERIC.</p>
377 </li></ol>
378
379 <p>Note that the order of the rules for determining column affinity
380 is important. A column whose declared type is "CHARINT" will match
381 both rules 1 and 2 but the first rule takes precedence and so the
382 column affinity will be INTEGER.</p>
383
384 <h3 id="affinity_name_examples"><span>3.1.1. </span>Affinity Name Examples</h3>
385
386 <p>The following table shows how many common datatype names from
387 more traditional SQL implementations are converted into affinities by the five rules of the
388 previous section. This table shows only a small subset of the
389 datatype names that SQLite will accept. Note that numeric arguments
390 in parentheses that following the type name (ex: "VARCHAR(255)") are
391 ignored by SQLite - SQLite does not impose any length restrictions
392 (other than the large global <a href="limits.html#max_length">SQLITE_MAX_LENGTH</a> limit) on the length of
393 strings, BLOBs or numeric values.</p>
394
395 <blockquote>
396 <table border="1" cellpadding="5">
397 <tr><th>Example Typenames From The<br>CREATE TABLE Statement<br>
398 or CAST Expression
399 </th><th>Resulting Affinity
400 </th><th>Rule Used To Determine Affinity
401
402 </th></tr><tr><td align="center" valign="top">
403 INT<br>
404 INTEGER<br>
405 TINYINT<br>
406 SMALLINT<br>
407 MEDIUMINT<br>
408 BIGINT<br>
409 UNSIGNED BIG INT<br>
410 INT2<br>
411 INT8
412 </td><td align="center">INTEGER
413 </td><td align="center">1
414
415 </td></tr><tr><td align="center" valign="top">
416 CHARACTER(20)<br>
417 VARCHAR(255)<br>
418 VARYING CHARACTER(255)<br>
419 NCHAR(55)<br>
420 NATIVE CHARACTER(70)<br>
421 NVARCHAR(100)<br>
422 TEXT<br>
423 CLOB
424 </td><td align="center">TEXT
425 </td><td align="center">2
426
427 </td></tr><tr><td align="center" valign="top">
428 BLOB<br>
429 <i>no datatype specified</i>
430 </td><td align="center">BLOB
431 </td><td align="center">3
432
433 </td></tr><tr><td align="center" valign="top">
434 REAL<br>
435 DOUBLE<br>
436 DOUBLE PRECISION<br>
437 FLOAT
438 </td><td align="center">REAL
439 </td><td align="center">4
440
441 </td></tr><tr><td align="center" valign="top">
442 NUMERIC<br>
443 DECIMAL(10,5)<br>
444 BOOLEAN<br>
445 DATE<br>
446 DATETIME
447 </td><td align="center">NUMERIC
448 </td><td align="center">5
449 </td></tr></table>
450 </blockquote>
451
452 <p>Note that a declared type of "FLOATING POINT" would give INTEGER
453 affinity, not REAL affinity, due to the "INT" at the end of "POINT".
454 And the declared type of "STRING" has an affinity of NUMERIC, not TEXT.
455
456 <a name="expraff"></a>
457
458 </p><h2 id="affinity_of_expressions"><span>3.2. </span>Affinity Of Expressions</h2>
459
460 <p>Every table column has a type affinity (one of BLOB, TEXT, INTEGER,
461 REAL, or NUMERIC) but expressions do not necessarily have an affinity.
462
463 </p><p>Expression affinity is determined by the following rules:
464
465 </p><ul>
466 <li><p>The right-hand operand of an IN or NOT IN
467 operator has no affinity if the operand is a list, or has the same
468 affinity as the affinity of the result set expression if the operand
469 is a SELECT.
470
471 </p></li><li><p>When an expression is a simple reference to a column of a
472 real table (not a <a href="lang_createview.html">VIEW</a> or subquery) then the expression
473 has the same affinity as the table column.
474 </p><ul>
475 <li><p>Parentheses around the column name are ignored. Hence if
476 X and Y.Z are column names, then (X) and (Y.Z) are also considered
477 column names and have the affinity of the corresponding columns.
478 </p></li><li><p>
479 Any operators applied to column names, including the no-op
480 unary "+" operator, convert the column name into an expression which
481 always has no affinity. Hence even if X and Y.Z are column names, the
482 expressions +X and +Y.Z are not column names and have no affinity.
483 </p></li></ul>
484
485 </li><li><p>An expression of the form "CAST(<i>expr</i> AS <i>type</i>)"
486 has an affinity that is the same as a column with a declared
487 type of "<i>type</i>".
488
489 </p></li><li><p>A COLLATE operator has the same affinity as its left-hand side operand.
490
491 </p></li><li><p>Otherwise, an expression has no affinity.
492 </p></li></ul>
493
494 <a name="affview"></a>
495
496 <h2 id="column_affinity_for_views_and_subqueries"><span>3.3. </span>Column Affinity For Views And Subqueries</h2>
497
498 <p>The "columns" of a <a href="lang_createview.html">VIEW</a> or FROM-clause subquery are really
499 the expressions
500 in the result set of the <a href="lang_select.html">SELECT</a> statement that implements the VIEW
501 or subquery. Thus, the affinity for columns of a VIEW or subquery
502 are determined by the expression affinity rules above.
503 Consider an example:
504
505 </p><blockquote><pre>
506 CREATE TABLE t1(a INT, b TEXT, c REAL);
507 CREATE VIEW v1(x,y,z) AS SELECT b, a+c, 42 FROM t1 WHERE b!=11;
508 </pre></blockquote>
509
510 <p>The affinity of the v1.x column will be the same as the affinity
511 of t1.b (TEXT), since v1.x maps directly into t1.b. But
512 columns v1.y and v1.z both have no affinity, since those columns
513 map into expression a+c and 42, and expressions always have no
514 affinity.
515
516 <a name="affcompoundview"></a>
517
518 </p><h3 id="column_affinity_for_compound_views"><span>3.3.1. </span>Column Affinity For Compound Views</h3>
519
520 <p>When the <a href="lang_select.html">SELECT</a> statement that implements a <a href="lang_createview.html">VIEW</a> or
521 FROM-clause subquery is a <a href="lang_select.html#compound">compound SELECT</a> then the affinity of
522 each column of the VIEW or subquery will
523 be the affinity of the corresponding result column for
524 one of the individual SELECT statements that make up
525 the compound.
526 However, it is indeterminate which of the SELECT statements will
527 be used to determine affinity.
528 Different constituent SELECT statements might be used to determine
529 affinity at different times during query evaluation. The choice
530 might vary across different versions of SQLite. The choice might
531 change between one query and the next in the same version of SQLite.
532 The choice might be different at different times within the same
533 query. Hence, you can never be sure what affinity will be used
534 for columns of a compound SELECT that have different affinities in
535 the constituent subqueries.
536
537 </p><p>Best practice is to avoid mixing affinities in a compound SELECT
538 if you care about the datatype of the result. Mixing affinities in
539 a compound SELECT can lead to surprising and unintuitive results.
540 See, for example, <a href="https://sqlite.org/forum/forumpost/02d7be94d7">forum post 02d7be94d7</a>.
541
542 </p><h2 id="column_affinity_behavior_example"><span>3.4. </span>Column Affinity Behavior Example</h2>
543
544 <p>The following SQL demonstrates how SQLite uses column affinity
545 to do type conversions when values are inserted into a table.</p>
546
547 <blockquote>
548 <pre>
549 CREATE TABLE t1(
550 t TEXT, -- text affinity by rule 2
551 nu NUMERIC, -- numeric affinity by rule 5
552 i INTEGER, -- integer affinity by rule 1
553 r REAL, -- real affinity by rule 4
554 no BLOB -- no affinity by rule 3
555 );
556
557 -- Values stored as TEXT, INTEGER, INTEGER, REAL, TEXT.
558 INSERT INTO t1 VALUES('500.0', '500.0', '500.0', '500.0', '500.0');
559 SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
560 text|integer|integer|real|text
561
562 -- Values stored as TEXT, INTEGER, INTEGER, REAL, REAL.
563 DELETE FROM t1;
564 INSERT INTO t1 VALUES(500.0, 500.0, 500.0, 500.0, 500.0);
565 SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
566 text|integer|integer|real|real
567
568 -- Values stored as TEXT, INTEGER, INTEGER, REAL, INTEGER.
569 DELETE FROM t1;
570 INSERT INTO t1 VALUES(500, 500, 500, 500, 500);
571 SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
572 text|integer|integer|real|integer
573
574 -- BLOBs are always stored as BLOBs regardless of column affinity.
575 DELETE FROM t1;
576 INSERT INTO t1 VALUES(x'0500', x'0500', x'0500', x'0500', x'0500');
577 SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
578 blob|blob|blob|blob|blob
579
580 -- NULLs are also unaffected by affinity
581 DELETE FROM t1;
582 INSERT INTO t1 VALUES(NULL,NULL,NULL,NULL,NULL);
583 SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
584 null|null|null|null|null
585 </pre>
586 </blockquote>
587
588 <a name="comparisons"></a>
589
590 <h1 id="comparison_expressions"><span>4. </span>Comparison Expressions</h1>
591
592 <p>SQLite version 3 has the usual set of SQL comparison operators
593 including "=", "==", "&lt;", "&lt;=", "&gt;", "&gt;=", "!=", "",
594 "IN", "NOT IN", "BETWEEN", "IS", and "IS NOT", .
595
596 <a name="sortorder"></a>
597
598 </p><h2 id="sort_order"><span>4.1. </span>Sort Order</h2>
599
600 <p>The results of a comparison depend on the storage classes of the
601 operands, according to the following rules:</p>
602 <ul>
603 <li><p>A value with storage class NULL is considered less than any
604 other value (including another value with storage class NULL).</p>
605
606 </li><li><p>An INTEGER or REAL value is less than any TEXT or BLOB value.
607 When an INTEGER or REAL is compared to another INTEGER or REAL, a
608 numerical comparison is performed.</p>
609
610 </li><li><p>A TEXT value is less than a BLOB value. When two TEXT values
611 are compared an appropriate collating sequence is used to determine
612 the result. </p>
613
614 </li><li><p>When two BLOB values are compared, the result is
615 determined using memcmp().</p>
616 </li></ul>
617
618 <a name="compaff"></a>
619
620 <h2 id="type_conversions_prior_to_comparison"><span>4.2. </span>Type Conversions Prior To Comparison</h2>
621
622 <p>SQLite may attempt to convert values between the storage classes
623 INTEGER, REAL, and/or TEXT before performing a comparison.
624 Whether or not any conversions are attempted before the comparison takes
625 place depends on the type affinity of the operands.
626
627 </p><p>To "apply affinity" means to convert an operand to a particular storage
628 class if and only if the conversion does not lose essential information.
629 Numeric values can always be converted into TEXT. TEXT values
630 can be converted into numeric values if the text content is a well-formed
631 integer or real literal, but not a hexadecimal integer literal.
632 BLOB values are converted into TEXT values by simply interpreting
633 the binary BLOB content as a text string in the current database
634 encoding.
635
636 </p><p>Affinity is applied to operands of a comparison operator prior to
637 the comparison according to the following rules in the order shown:</p>
638
639 <ul>
640 <li><p>If one operand has INTEGER, REAL or NUMERIC affinity
641 and the other operand has TEXT or BLOB or no affinity
642 then NUMERIC affinity is applied to the other operand.
643
644 </p></li><li><p>If one operand has TEXT affinity and the other has no affinity,
645 then TEXT affinity is applied to the other operand.
646
647 </p></li><li><p>Otherwise, no affinity is applied and both operands are compared
648 as is.</p>
649 </li></ul>
650
651 <p>The expression "a BETWEEN b AND c" is treated as two separate
652 binary comparisons "a &gt;= b AND a &lt;= c", even if that means
653 different affinities are applied to 'a' in each of the comparisons.
654 Datatype conversions in comparisons of the
655 form "x IN (SELECT y ...)" are handled as if
656 the comparison were really "x=y".
657 The expression "a IN (x, y, z, ...)" is equivalent to "a = +x OR
658 a = +y OR a = +z OR ...".
659 In other words, the values to the right of the IN operator (the "x", "y",
660 and "z" values in this example) are considered to have no affinity,
661 even if they happen to be column values or CAST expressions.
662 </p>
663
664 <h2 id="comparison_example"><span>4.3. </span>Comparison Example</h2>
665
666 <blockquote>
667 <pre>
668 CREATE TABLE t1(
669 a TEXT, -- text affinity
670 b NUMERIC, -- numeric affinity
671 c BLOB, -- no affinity
672 d -- no affinity
673 );
674
675 -- Values will be stored as TEXT, INTEGER, TEXT, and INTEGER respectively
676 INSERT INTO t1 VALUES('500', '500', '500', 500);
677 SELECT typeof(a), typeof(b), typeof(c), typeof(d) FROM t1;
678 text|integer|text|integer
679
680 -- Because column "a" has text affinity, numeric values on the
681 -- right-hand side of the comparisons are converted to text before
682 -- the comparison occurs.
683 SELECT a &lt; 40, a &lt; 60, a &lt; 600 FROM t1;
684 0|1|1
685
686 -- Text affinity is applied to the right-hand operands but since
687 -- they are already TEXT this is a no-op; no conversions occur.
688 SELECT a &lt; '40', a &lt; '60', a &lt; '600' FROM t1;
689 0|1|1
690
691 -- Column "b" has numeric affinity and so numeric affinity is applied
692 -- to the operands on the right. Since the operands are already numeric,
693 -- the application of affinity is a no-op; no conversions occur. All
694 -- values are compared numerically.
695 SELECT b &lt; 40, b &lt; 60, b &lt; 600 FROM t1;
696 0|0|1
697
698 -- Numeric affinity is applied to operands on the right, converting them
699 -- from text to integers. Then a numeric comparison occurs.
700 SELECT b &lt; '40', b &lt; '60', b &lt; '600' FROM t1;
701 0|0|1
702
703 -- No affinity conversions occur. Right-hand side values all have
704 -- storage class INTEGER which are always less than the TEXT values
705 -- on the left.
706 SELECT c &lt; 40, c &lt; 60, c &lt; 600 FROM t1;
707 0|0|0
708
709 -- No affinity conversions occur. Values are compared as TEXT.
710 SELECT c &lt; '40', c &lt; '60', c &lt; '600' FROM t1;
711 0|1|1
712
713 -- No affinity conversions occur. Right-hand side values all have
714 -- storage class INTEGER which compare numerically with the INTEGER
715 -- values on the left.
716 SELECT d &lt; 40, d &lt; 60, d &lt; 600 FROM t1;
717 0|0|1
718
719 -- No affinity conversions occur. INTEGER values on the left are
720 -- always less than TEXT values on the right.
721 SELECT d &lt; '40', d &lt; '60', d &lt; '600' FROM t1;
722 1|1|1
723 </pre>
724 </blockquote>
725
726 <p>All of the results in the example are the same if the comparisons are
727 commuted - if expressions of the form "a&lt;40" are rewritten
728 as "40&gt;a".
729
730 </p><h1 id="operators"><span>5. </span>Operators</h1>
731
732 <p>Mathematical operators (+, -, *, /, %, &lt;&lt;, &gt;&gt;,
733 &amp;, and |) interpret both operands as if they were numbers.
734 STRING or BLOB operands automatically convert into REAL or INTEGER values.
735 If the STRING or BLOB looks like a real number (if it has a decimal point
736 or an exponent) or if the value is outside the range that can be represented
737 as a 64-bit signed integer, then it converts to REAL. Otherwise the operand
738 converts to INTEGER.
739 The implied type conversion of mathematical operands is slightly different
740 from <a href="lang_expr.html#castexpr">CAST to NUMERIC</a> in that string and BLOB values that
741 look like real numbers but have no fractional part are kept as REAL
742 instead of being converted into INTEGER as they would be for <a href="lang_expr.html#castexpr">CAST to NUMERIC</a>.
743 The conversion from STRING or BLOB into REAL or INTEGER is performed
744 even if it is lossy and irreversible.
745 Some mathematical operators (%, &lt;&lt;, &gt;&gt;, &amp;, and |) expect
746 INTEGER operands. For those operators, REAL operands are converted into INTEGER
747 in the same way as a <a href="lang_expr.html#castexpr">CAST to INTEGER</a>.
748 The &lt;&lt;, &gt;&gt;, &amp;, and | operators always return an INTEGER (or NULL)
749 result, but the % operator returns either INTEGER or REAL (or NULL)
750 depending on the type of its operands.
751 A NULL operand on a mathematical operator yields a NULL result.
752 An operand on a mathematical operator that does not look in any way
753 numeric and is not NULL is converted to 0 or 0.0.
754 Division by zero gives a result of NULL.
755 </p>
756
757 <h1 id="sorting_grouping_and_compound_selects"><span>6. </span>Sorting, Grouping and Compound SELECTs</h1>
758
759 <p>When query results are sorted by an ORDER BY clause, values with storage
760 class NULL come first, followed by INTEGER and REAL values
761 interspersed in numeric order, followed by TEXT values in collating
762 sequence order, and finally BLOB values in memcmp() order. No storage
763 class conversions occur before the sort.</p>
764
765 <p>When grouping values with the GROUP BY clause values with
766 different storage classes are considered distinct, except for INTEGER
767 and REAL values which are considered equal if they are numerically
768 equal. No affinities are applied to any values as the result of a
769 GROUP by clause.</p>
770
771 <p>The compound SELECT operators UNION,
772 INTERSECT and EXCEPT perform implicit comparisons between values.
773 No affinity is applied to comparison operands for the implicit
774 comparisons associated with UNION, INTERSECT, or EXCEPT - the values
775 are compared as is.</p>
776
777 <a name="collation"></a>
778
779 <h1 id="collating_sequences"><span>7. </span>Collating Sequences</h1>
780
781 <p>When SQLite compares two strings, it uses a collating sequence or
782 collating function (two terms for the same thing) to determine which
783 string is greater or if the two strings are equal.
784 SQLite has three built-in collating functions: BINARY, NOCASE, and
785 RTRIM.</p>
786
787 <ul>
788 <li><b>BINARY</b> - Compares string data using memcmp(), regardless
789 of text encoding.</li>
790 <li><b>NOCASE</b> - Similar to binary, except that it uses
791 <a href="c3ref/stricmp.html">sqlite3_strnicmp()</a> for the comparison. Hence the 26 upper case
792 characters of ASCII are folded to their lower case equivalents before
793 the comparison is performed. Note that only ASCII characters
794 are case folded. SQLite does not attempt to do full
795 UTF case folding due to the size of the tables required.
796 Also note that any U+0000 characters in the string are considered
797 string terminators for comparison purposes.
798
799 </li><li><b>RTRIM</b> - The same as binary, except that trailing space
800 characters are ignored.</li>
801 </ul>
802
803 <p>An application can register additional collating functions using
804 the <a href="c3ref/create_collation.html">sqlite3_create_collation()</a> interface.</p>
805
806 <p>Collating functions only matter when comparing string values.
807 Numeric values are always compared numerically, and BLOBs are always
808 compared byte-by-byte using memcmp().</p>
809
810 <h2 id="assigning_collating_sequences_from_sql"><span>7.1. </span>Assigning Collating Sequences from SQL</h2>
811
812 <p>
813 Every column of every
814 table has an associated collating function. If no collating function
815 is explicitly defined, then the collating function defaults to BINARY.
816 The COLLATE clause of the <a href="lang_createtable.html#tablecoldef">column definition</a> is used
817 to define alternative collating functions for a column.
818 </p>
819
820 <a name="colrules"></a>
821
822
823 <p>
824 The rules for determining which collating function to use for a
825 binary comparison operator (=, &lt;, &gt;, &lt;=, &gt;=, !=, IS, and
826 IS NOT) are as follows:
827
828 </p><ol>
829 <li><p>If either operand has an explicit collating function assignment
830 using the postfix <a href="lang_expr.html#collateop">COLLATE operator</a>, then the explicit collating function
831 is used for comparison, with precedence to the collating function of the
832 left operand.</p></li>
833
834 <li><p>If either operand is a column, then the collating function of
835 that column is used with precedence to the left operand.
836 For the purposes of the previous sentence, a column name
837 preceded by one or more unary "+" operators and/or CAST operators
838 is still considered a column name.
839 </p></li>
840
841 <li><p>Otherwise, the BINARY collating function is used for comparison.
842 </p></li>
843 </ol>
844
845 <p>
846 An operand of a comparison is considered to have an explicit
847 collating function assignment (rule 1 above)
848 if any subexpression of the operand uses
849 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
850 anywhere in a comparison expression, the collating function defined
851 by that operator is used for string comparison regardless of what
852 table columns might be a part of that expression. If two or more
853 <a href="lang_expr.html#collateop">COLLATE operator</a> subexpressions appear anywhere in a comparison, the
854 left most explicit collating function is used regardless of how deeply the
855 COLLATE operators are nested in the expression and regardless of
856 how the expression is parenthesized.
857 </p>
858
859 <p>
860 The expression "x BETWEEN y and z" is logically
861 equivalent to two comparisons "x &gt;= y AND x &lt;= z" and works with
862 respect to collating functions as if it were two separate comparisons.
863 The expression "x IN (SELECT y ...)" is handled in the same way as the
864 expression "x = y" for the purposes of determining the collating sequence.
865 The collating sequence used for expressions of the form
866 "x IN (y, z, ...)" is the collating sequence of x.
867 If an explicit collating sequence is required on an IN operator
868 it should be applied to the left operand, like this:
869 "x COLLATE nocase IN (y,z, ...)".
870 </p>
871
872 <p>
873 Terms of the ORDER BY clause that is part of a <a href="lang_select.html">SELECT</a>
874 statement may be assigned a collating sequence using the
875 <a href="lang_expr.html#collateop">COLLATE operator</a>, in which case the specified collating function is
876 used for sorting.
877 Otherwise, if the expression sorted by an ORDER BY clause is
878 a column, then the collating sequence of the column is used to
879 determine sort order. If the expression is not a column and has no
880 COLLATE clause, then the BINARY collating sequence is used.
881 </p>
882
883 <h2 id="collation_sequence_examples"><span>7.2. </span>Collation Sequence Examples</h2>
884 <p>
885 The examples below identify the collating sequences that would be used to
886 determine the results of text comparisons that may be performed by various
887 SQL statements. Note that a text comparison may not be required, and no
888 collating sequence used, in the case of numeric, blob or NULL values.
889 </p>
890 <blockquote>
891 <pre>
892 CREATE TABLE t1(
893 x INTEGER PRIMARY KEY,
894 a, /* collating sequence BINARY */
895 b COLLATE BINARY, /* collating sequence BINARY */
896 c COLLATE RTRIM, /* collating sequence RTRIM */
897 d COLLATE NOCASE /* collating sequence NOCASE */
898 );
899 /* x a b c d */
900 INSERT INTO t1 VALUES(1,'abc','abc', 'abc ','abc');
901 INSERT INTO t1 VALUES(2,'abc','abc', 'abc', 'ABC');
902 INSERT INTO t1 VALUES(3,'abc','abc', 'abc ', 'Abc');
903 INSERT INTO t1 VALUES(4,'abc','abc ','ABC', 'abc');
904
905 /* Text comparison a=b is performed using the BINARY collating sequence. */
906 SELECT x FROM t1 WHERE a = b ORDER BY x;
907 --result 1 2 3
908
909 /* Text comparison a=b is performed using the RTRIM collating sequence. */
910 SELECT x FROM t1 WHERE a = b COLLATE RTRIM ORDER BY x;
911 --result 1 2 3 4
912
913 /* Text comparison d=a is performed using the NOCASE collating sequence. */
914 SELECT x FROM t1 WHERE d = a ORDER BY x;
915 --result 1 2 3 4
916
917 /* Text comparison a=d is performed using the BINARY collating sequence. */
918 SELECT x FROM t1 WHERE a = d ORDER BY x;
919 --result 1 4
920
921 /* Text comparison 'abc'=c is performed using the RTRIM collating sequence. */
922 SELECT x FROM t1 WHERE 'abc' = c ORDER BY x;
923 --result 1 2 3
924
925 /* Text comparison c='abc' is performed using the RTRIM collating sequence. */
926 SELECT x FROM t1 WHERE c = 'abc' ORDER BY x;
927 --result 1 2 3
928
929 /* Grouping is performed using the NOCASE collating sequence (Values
930 ** 'abc', 'ABC', and 'Abc' are placed in the same group). */
931 SELECT count(*) FROM t1 GROUP BY d ORDER BY 1;
932 --result 4
933
934 /* Grouping is performed using the BINARY collating sequence. 'abc' and
935 ** 'ABC' and 'Abc' form different groups */
936 SELECT count(*) FROM t1 GROUP BY (d || '') ORDER BY 1;
937 --result 1 1 2
938
939 /* Sorting or column c is performed using the RTRIM collating sequence. */
940 SELECT x FROM t1 ORDER BY c, x;
941 --result 4 1 2 3
942
943 /* Sorting of (c||'') is performed using the BINARY collating sequence. */
944 SELECT x FROM t1 ORDER BY (c||''), x;
945 --result 4 2 3 1
946
947 /* Sorting of column c is performed using the NOCASE collating sequence. */
948 SELECT x FROM t1 ORDER BY c COLLATE NOCASE, x;
949 --result 2 4 3 1
950 </pre>
951 </blockquote>
952 <p align="center"><small><i>This page was last updated on 2026-02-18 13:39:01Z </small></i></p>
953