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>SQLite Query Language: Core Functions</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>
120
<h1 align="center">SQL As Understood By SQLite</h1><p><a href="lang.html">[Top]</a></p><h2>Core Functions</h2>
122
<p>The core functions shown below are available by default.
123
<a href="lang_datefunc.html">Date & Time functions</a> and
124
<a href="lang_aggfunc.html">aggregate functions</a> are documented separately. An
125
application may define additional
126
functions written in C and added to the database engine using
127
the <a href="c3ref/create_function.html">sqlite3_create_function()</a> API.</p>
129
<table border=0 cellpadding=10>
130
<tr><td valign="top" align="right" width="120"><a name="abs"></a>
131
abs(<i>X</i>)</td><td valign="top">
132
The abs(X) function returns the absolute value of the numeric
133
argument X. Abs(X) returns NULL if X is NULL.
134
Abs(X) return 0.0 if X is a string or blob
135
that cannot be converted to a numeric value. If X is the
136
integer -9223372036854775807 then abs(X) throws an integer overflow
137
error since there is no equivalent positive 64-bit two complement value.
138
</td></tr><tr><td valign="top" align="right" width="120"><a name="changes"></a>
139
changes()</td><td valign="top">
140
The changes() function returns the number of database rows that were changed
141
or inserted or deleted by the most recently completed INSERT, DELETE,
142
or UPDATE statement, exclusive of statements in lower-level triggers.
143
The changes() SQL function is a wrapper around the <a href="c3ref/changes.html">sqlite3_changes()</a>
144
C/C++ function and hence follows the same rules for counting changes.
145
</td></tr><tr><td valign="top" align="right" width="120"><a name="coalesce"></a>
146
coalesce(<i>X</i>,<i>Y</i>,...)</td><td valign="top">
147
The coalesce() function returns a copy of its first non-NULL argument, or
148
NULL if all arguments are NULL. Coalesce() must be at least
150
</td></tr><tr><td valign="top" align="right" width="120"><a name="glob"></a>
151
glob(<i>X</i>,<i>Y</i>)</td><td valign="top">
152
The glob(X,Y) function is equivalent to the
153
expression "<b>Y GLOB X</b>".
154
Note that the X and Y arguments are reversed in the glob() function
155
relative to the infix <a href="lang_expr.html#glob">GLOB</a> operator.
156
If the <a href="c3ref/create_function.html">sqlite3_create_function()</a> interface is used to
157
override the glob(X,Y) function with an alternative implementation then
158
the <a href="lang_expr.html#glob">GLOB</a> operator will invoke the alternative implementation.
159
</td></tr><tr><td valign="top" align="right" width="120"><a name="ifnull"></a>
160
ifnull(<i>X</i>,<i>Y</i>)</td><td valign="top">
161
The ifnull() function returns a copy of its first non-NULL argument, or
162
NULL if both arguments are NULL. Ifnull() must have exactly 2 arguments.
163
The ifnull() function is equivalent to <a href="lang_corefunc.html#coalesce">coalesce()</a> with two arguments.
164
</td></tr><tr><td valign="top" align="right" width="120"><a name="hex"></a>
165
hex(<i>X</i>)</td><td valign="top">
166
The hex() function interprets its argument as a BLOB and returns
167
a string which is the upper-case hexadecimal rendering of the content of
169
</td></tr><tr><td valign="top" align="right" width="120"><a name="last_insert_rowid"></a>
170
last_insert_rowid()</td><td valign="top">
171
The last_insert_rowid() function returns the <a href="lang_createtable.html#rowid">ROWID</a>
172
of the last row insert from the database connection which invoked the
174
The last_insert_rowid() SQL function is a wrapper around the
175
<a href="c3ref/last_insert_rowid.html">sqlite3_last_insert_rowid()</a> C/C++ interface function.
176
</td></tr><tr><td valign="top" align="right" width="120"><a name="length"></a>
177
length(<i>X</i>)</td><td valign="top">
178
For a string value X, the length(X) function returns the number of
179
characters (not bytes) in X prior to the first NUL character.
180
Since SQLite strings do not normally contain NUL characters, the length(X)
181
function will usually return the total number of characters in the string X.
182
For a blob value X, length(X) returns the number of bytes in the blob.
183
If X is NULL then length(X) is NULL.
184
If X is numeric then length(X) returns the length of a string
186
</td></tr><tr><td valign="top" align="right" width="120"><a name="like"></a>
187
like(<i>X</i>,<i>Y</i>)<br></br>like(<i>X</i>,<i>Y</i>,<i>Z</i>)</td><td valign="top">
188
The like() function is used to implement the
189
"<b>Y LIKE X [ESCAPE Z]</b>" expression.
190
If the optional ESCAPE clause is present, then the
191
like() function is invoked with three arguments. Otherwise, it is
192
invoked with two arguments only. Note that the X and Y parameters are
193
reversed in the like() function relative to the infix <a href="lang_expr.html#like">LIKE</a> operator.
194
The <a href="c3ref/create_function.html">sqlite3_create_function()</a> interface can be used to override the
195
like() function and thereby change the operation of the
196
<a href="lang_expr.html#like">LIKE</a> operator. When overriding the like() function, it may be important
197
to override both the two and three argument versions of the like()
198
function. Otherwise, different code may be called to implement the
199
<a href="lang_expr.html#like">LIKE</a> operator depending on whether or not an ESCAPE clause was
201
</td></tr><tr><td valign="top" align="right" width="120"><a name="load_extension"></a>
202
load_extension(<i>X</i>)<br></br>load_extension(<i>X</i>,<i>Y</i>)</td><td valign="top">
203
The load_extension(X,Y) function loads SQLite extensions out of the shared
204
library file named X using the entry point Y. The result of load_extension()
205
is always a NULL. If Y is omitted then the default entry point
206
of <b>sqlite3_extension_init</b> is used. The load_extension() function
207
raises an exception if the extension fails to load or initialize correctly.
209
<p>The load_extension() function will fail if the extension attempts to
210
modify or delete an SQL function or collating sequence. The
211
extension can add new functions or collating sequences, but cannot
212
modify or delete existing functions or collating sequences because
213
those functions and/or collating sequences might be used elsewhere
214
in the currently running SQL statement. To load an extension that
215
changes or deletes functions or collating sequences, use the
216
<a href="c3ref/load_extension.html">sqlite3_load_extension()</a> C-language API.</p>
217
</td></tr><tr><td valign="top" align="right" width="120"><a name="lower"></a>
218
lower(<i>X</i>)</td><td valign="top">
219
The lower(X) function returns a copy of string X with all ASCII characters
220
converted to lower case. The default built-in lower() function works
221
for ASCII characters only. To do case conversions on non-ASCII
222
characters, load the ICU extension.
223
</td></tr><tr><td valign="top" align="right" width="120"><a name="ltrim"></a>
224
ltrim(<i>X</i>)<br></br>ltrim(<i>X</i>,<i>Y</i>)</td><td valign="top">
225
The ltrim(X,Y) function returns a string formed by removing any and all
226
characters that appear in Y from the left side of X.
227
If the Y argument is omitted, ltrim(X) removes spaces from the left side
229
</td></tr><tr><td valign="top" align="right" width="120"><a name="maxoreunc"></a>
230
max(<i>X</i>,<i>Y</i>,...)</td><td valign="top">
231
The multi-argument max() function returns the argument with the
232
maximum value, or return NULL if any argument is NULL.
233
The multi-argument max() function searches its arguments from left to right
234
for an argument that defines a collating function and uses that collating
235
function for all string comparisons. If none of the arguments to max()
236
define a collating function, then the BINARY collating function is used.
237
Note that <b>max()</b> is a simple function when
238
it has 2 or more arguments but operates as an
239
<a href="lang_aggfunc.html#minggunc">aggregate function</a> if given only a single argument.
240
</td></tr><tr><td valign="top" align="right" width="120"><a name="minoreunc"></a>
241
min(<i>X</i>,<i>Y</i>,...)</td><td valign="top">
242
The multi-argument min() function returns the argument with the
244
The multi-argument min() function searches its arguments from left to right
245
for an argument that defines a collating function and uses that collating
246
function for all string comparisons. If none of the arguments to min()
247
define a collating function, then the BINARY collating function is used.
248
Note that <b>min()</b> is a simple function when
249
it has 2 or more arguments but operates as an
250
<a href="lang_aggfunc.html#maxggunc">aggregate function</a> if given
251
only a single argument.
252
</td></tr><tr><td valign="top" align="right" width="120"><a name="nullif"></a>
253
nullif(<i>X</i>,<i>Y</i>)</td><td valign="top">
254
The nullif(X,Y) function returns its first argument if the arguments are
255
different and NULL if the arguments are the same. The nullif(X,Y) function
256
searches its arguments from left to right for an argument that defines a
257
collating function and uses that collating function for all string
258
comparisons. If neither argument to nullif() defines a collating function
259
then the BINARY is used.
260
</td></tr><tr><td valign="top" align="right" width="120"><a name="quote"></a>
261
quote(<i>X</i>)</td><td valign="top">
262
The quote(X) function returns the text of an SQL literal which
263
is the value of its argument suitable for inclusion into an SQL statement.
264
Strings are surrounded by single-quotes with escapes on interior quotes
265
as needed. BLOBs are encoded as hexadecimal literals.
266
Strings with embedded NUL characters cannot be represented as string
267
literals in SQL and hence the returned string literal is truncated prior
269
</td></tr><tr><td valign="top" align="right" width="120"><a name="random"></a>
270
random()</td><td valign="top">
271
The random() function returns a pseudo-random integer
272
between -9223372036854775808 and +9223372036854775807.
273
</td></tr><tr><td valign="top" align="right" width="120"><a name="randomblob"></a>
274
randomblob(<i>N</i>)</td><td valign="top">
275
The randomblob(N) function return an N-byte blob containing pseudo-random
276
bytes. If N is less than 1 then a 1-byte random blob is returned.
278
<p>Hint: applications can generate globally unique identifiers
279
using this function together with <a href="lang_corefunc.html#hex">hex()</a> and/or
280
<a href="lang_corefunc.html#lower">lower()</a> like this:</p>
283
hex(randomblob(16))<br></br>
284
lower(hex(randomblob(16)))
286
</td></tr><tr><td valign="top" align="right" width="120"><a name="replace"></a>
287
replace(<i>X</i>,<i>Y</i>,<i>Z</i>)</td><td valign="top">
288
The replace(X,Y,Z) function returns a string formed by substituting
289
string Z for every occurrence of string Y in string X. The <a href="datatype3.html#collation">BINARY</a>
290
collating sequence is used for comparisons. If Y is an empty
291
string then return X unchanged. If Z is not initially
292
a string, it is cast to a UTF-8 string prior to processing.
293
</td></tr><tr><td valign="top" align="right" width="120"><a name="round"></a>
294
round(<i>X</i>)<br></br>round(<i>X</i>,<i>Y</i>)</td><td valign="top">
295
The round(X,Y) function returns a floating-point
296
value X rounded to Y digits to the right of the decimal point.
297
If the Y argument is omitted, it is assumed to be 0.
298
</td></tr><tr><td valign="top" align="right" width="120"><a name="rtrim"></a>
299
rtrim(<i>X</i>)<br></br>rtrim(<i>X</i>,<i>Y</i>)</td><td valign="top">
300
The rtrim(X,Y) function returns a string formed by removing any and all
301
characters that appear in Y from the right side of X.
302
If the Y argument is omitted, rtrim(X) removes spaces from the right
304
</td></tr><tr><td valign="top" align="right" width="120"><a name="soundex"></a>
305
soundex(<i>X</i>)</td><td valign="top">
306
The soundex(X) function returns a string that is the soundex encoding
308
The string "?000" is returned if the argument is NULL or contains
309
no ASCII alphabetic characters.
310
This function is omitted from SQLite by default.
311
It is only available if the <a href="compile.html#soundex">SQLITE_SOUNDEX</a> compile-time option
312
is used when SQLite is built.
313
</td></tr><tr><td valign="top" align="right" width="120"><a name="sqlite_compileoption_get"></a>
314
sqlite_compileoption_get(<i>N</i>)</td><td valign="top">
315
The sqlite_compileoption_get() SQL function is a wrapper around the
316
<a href="c3ref/compileoption_get.html">sqlite3_compileoption_get()</a> C/C++ function.
317
This routine returns the N-th compile-time option used to build SQLite
318
or NULL if N is out of range. See also the <a href="pragma.html#pragma_compile_options">compile_options pragma</a>.
319
</td></tr><tr><td valign="top" align="right" width="120"><a name="sqlite_compileoption_used"></a>
320
sqlite_compileoption_used(<i>X</i>)</td><td valign="top">
321
The sqlite_compileoption_used() SQL function is a wrapper around the
322
<a href="c3ref/compileoption_get.html">sqlite3_compileoption_used()</a> C/C++ function.
323
When the argument X to sqlite_compileoption_used(X) is a string which
324
is the name of a compile-time option, this routine returns true (1) or
325
false (0) depending on whether or not that option was used during the
327
</td></tr><tr><td valign="top" align="right" width="120"><a name="sqlite_source_id"></a>
328
sqlite_source_id()</td><td valign="top">
329
The sqlite_source_id() function returns a string that identifies the
330
specific version of the source code that was used to build the SQLite
331
library. The string returned by sqlite_source_id() begins with
332
the date and time that the source code was checked in and is follows by
333
an SHA1 hash that uniquely identifies the source tree. This function is
334
an SQL wrapper around the <a href="c3ref/libversion.html">sqlite3_sourceid()</a> C interface.
335
</td></tr><tr><td valign="top" align="right" width="120"><a name="sqlite_version"></a>
336
sqlite_version()</td><td valign="top">
337
The sqlite_version() function returns the version string for the SQLite
338
library that is running. This function is an SQL
339
wrapper around the <a href="c3ref/libversion.html">sqlite3_libversion()</a> C-interface.
340
</td></tr><tr><td valign="top" align="right" width="120"><a name="substr"></a>
341
substr(<i>X</i>,<i>Y</i>,<i>Z</i>)<br></br>substr(<i>X</i>,<i>Y</i>)</td><td valign="top">
342
The substr(X,Y,Z) function returns a substring of input string X that begins
343
with the Y-th character and which is Z characters long.
344
If Z is omitted then substr(X,Y) returns all characters through the end
345
of the string X beginning with the Y-th.
346
The left-most character of X is number 1. If Y is negative
347
then the first character of the substring is found by counting from the
348
right rather than the left. If Z is negative then
349
the abs(Z) characters preceding the Y-th character are returned.
350
If X is a string then characters indices refer to actual UTF-8
351
characters. If X is a BLOB then the indices refer to bytes.
352
</td></tr><tr><td valign="top" align="right" width="120"><a name="total_changes"></a>
353
total_changes()</td><td valign="top">
354
The total_changes() function returns the number of row changes
355
caused by INSERT, UPDATE or DELETE
356
statements since the current database connection was opened.
357
This function is a wrapper around the <a href="c3ref/total_changes.html">sqlite3_total_changes()</a>
359
</td></tr><tr><td valign="top" align="right" width="120"><a name="trim"></a>
360
trim(<i>X</i>)<br></br>trim(<i>X</i>,<i>Y</i>)</td><td valign="top">
361
The trim(X,Y) function returns a string formed by removing any and all
362
characters that appear in Y from both ends of X.
363
If the Y argument is omitted, trim(X) removes spaces from both ends of X.
364
</td></tr><tr><td valign="top" align="right" width="120"><a name="typeof"></a>
365
typeof(<i>X</i>)</td><td valign="top">
366
The typeof(X) function returns a string that indicates the <a href="datatype3.html">datatype</a> of
367
the expression X: "null", "integer", "real", "text", or "blob".
368
</td></tr><tr><td valign="top" align="right" width="120"><a name="upper"></a>
369
upper(<i>X</i>)</td><td valign="top">
370
The upper(X) function returns a copy of input string X in which all
371
lower-case ASCII characters are converted to their upper-case equivalent.
372
</td></tr><tr><td valign="top" align="right" width="120"><a name="zeroblob"></a>
373
zeroblob(<i>N</i>)</td><td valign="top">
374
The zeroblob(N) function returns a BLOB consisting of N bytes of 0x00.
375
SQLite manages these zeroblobs very efficiently. Zeroblobs can be used to
376
reserve space for a BLOB that is later written using
377
<a href="c3ref/blob_open.html">incremental BLOB I/O</a>.
378
This SQL function is implemented using the <a href="c3ref/result_blob.html">sqlite3_result_zeroblob()</a>
379
routine from the C/C++ interface.
added
removed
lang_corefunc.html
