1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
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
161
162
163
164
165
166
167
168
169
170
171
172
173
174
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
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
315
316
317
318
319
320
321
322
323
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
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
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
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
|
<!DOCTYPE html>
<html><head>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<link href="sqlite.css" rel="stylesheet">
<title>Built-In Scalar SQL Functions</title>
<!-- path= -->
</head>
<body>
<div class=nosearch>
<a href="index.html">
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0">
</a>
<div><!-- IE hack to prevent disappearing logo --></div>
<div class="tagline desktoponly">
Small. Fast. Reliable.<br>Choose any three.
</div>
<div class="menu mainmenu">
<ul>
<li><a href="index.html">Home</a>
<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
<li class='wideonly'><a href='about.html'>About</a>
<li class='desktoponly'><a href="docs.html">Documentation</a>
<li class='desktoponly'><a href="download.html">Download</a>
<li class='wideonly'><a href='copyright.html'>License</a>
<li class='desktoponly'><a href="support.html">Support</a>
<li class='desktoponly'><a href="prosupport.html">Purchase</a>
<li class='search' id='search_menubutton'>
<a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
</ul>
</div>
<div class="menu submenu" id="submenu">
<ul>
<li><a href='about.html'>About</a>
<li><a href='docs.html'>Documentation</a>
<li><a href='download.html'>Download</a>
<li><a href='support.html'>Support</a>
<li><a href='prosupport.html'>Purchase</a>
</ul>
</div>
<div class="searchmenu" id="searchmenu">
<form method="GET" action="search">
<select name="s" id="searchtype">
<option value="d">Search Documentation</option>
<option value="c">Search Changelog</option>
</select>
<input type="text" name="q" id="searchbox" value="">
<input type="submit" value="Go">
</form>
</div>
</div>
<script>
function toggle_div(nm) {
var w = document.getElementById(nm);
if( w.style.display=="block" ){
w.style.display = "none";
}else{
w.style.display = "block";
}
}
function toggle_search() {
var w = document.getElementById("searchmenu");
if( w.style.display=="block" ){
w.style.display = "none";
} else {
w.style.display = "block";
setTimeout(function(){
document.getElementById("searchbox").focus()
}, 30);
}
}
function div_off(nm){document.getElementById(nm).style.display="none";}
window.onbeforeunload = function(e){div_off("submenu");}
/* Disable the Search feature if we are not operating from CGI, since */
/* Search is accomplished using CGI and will not work without it. */
if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
document.getElementById("search_menubutton").style.display = "none";
}
/* Used by the Hide/Show button beside syntax diagrams, to toggle the */
function hideorshow(btn,obj){
var x = document.getElementById(obj);
var b = document.getElementById(btn);
if( x.style.display!='none' ){
x.style.display = 'none';
b.innerHTML='show';
}else{
x.style.display = '';
b.innerHTML='hide';
}
return false;
}
var antiRobot = 0;
function antiRobotGo(){
if( antiRobot!=3 ) return;
antiRobot = 7;
var j = document.getElementById("mtimelink");
if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
}
function antiRobotDefense(){
document.body.onmousedown=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousedown=null;
}
document.body.onmousemove=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousemove=null;
}
setTimeout(function(){
antiRobot |= 1;
antiRobotGo();
}, 100)
antiRobotGo();
}
antiRobotDefense();
</script>
<div class=fancy>
<div class=nosearch>
<div class="fancy_title">
Built-In Scalar SQL Functions
</div>
</div>
<h1 id="overview"><span>1. </span>Overview</h1>
<p>The core functions shown below are available by default.
<a href="lang_datefunc.html">Date & Time functions</a>,
<a href="lang_aggfunc.html">aggregate functions</a>,
<a href="windowfunctions.html">window functions</a>,
<a href="lang_mathfunc.html">math functions</a>, and
<a href="json1.html">JSON functions</a> are documented separately. An
application may define additional
functions written in C and added to the database engine using
the <a href="c3ref/create_function.html">sqlite3_create_function()</a> API.</p>
<p><b><a href="syntax/simple-function-invocation.html">simple-function-invocation:</a></b></p><div class='imgcontainer'>
<div style="max-width:414px"><svg xmlns='http://www.w3.org/2000/svg' class="pikchr" viewBox="0 0 414.49 126.792">
<circle cx="5" cy="55" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="32,55 20,59 20,50" style="fill:rgb(0,0,0)"/>
<path d="M9,55L26,55" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M47,70L126,70A15 15 0 0 0 141 55A15 15 0 0 0 126 39L47,39A15 15 0 0 0 32 55A15 15 0 0 0 47 70Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="86" y="55" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">simple-func</text>
<polygon points="164,55 152,59 152,50" style="fill:rgb(0,0,0)"/>
<path d="M141,55L158,55" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M179,70A15 15 0 0 0 194 55A15 15 0 0 0 179 39A15 15 0 0 0 164 55A15 15 0 0 0 179 70Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="179" y="55" text-anchor="middle" font-weight="bold" fill="rgb(0,0,0)" dominant-baseline="central">(</text>
<polygon points="248,55 237,59 237,50" style="fill:rgb(0,0,0)"/>
<path d="M194,55L242,55" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M248,70L297,70L297,39L248,39Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="273" y="55" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">expr</text>
<polygon points="351,55 340,59 340,50" style="fill:rgb(0,0,0)"/>
<path d="M297,55L346,55" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M366,70A15 15 0 0 0 382 55A15 15 0 0 0 366 39A15 15 0 0 0 351 55A15 15 0 0 0 366 70Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="366" y="55" text-anchor="middle" font-weight="bold" fill="rgb(0,0,0)" dominant-baseline="central">)</text>
<polygon points="405,55 393,59 393,50" style="fill:rgb(0,0,0)"/>
<path d="M382,55L399,55" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<circle cx="408" cy="55" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M273,32A15 15 0 0 0 288 17L288,17A15 15 0 0 0 273 2A15 15 0 0 0 258 17L258,17A15 15 0 0 0 273 32Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="273" y="17" text-anchor="middle" font-weight="bold" fill="rgb(0,0,0)" dominant-baseline="central">,</text>
<polygon points="288,17 299,12 299,21" style="fill:rgb(0,0,0)"/>
<path d="M297,55 L 305,55 Q 312,55 312,40 L 312,32 Q 312,17 303,17 L 294,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M258,17 L 237,17 Q 222,17 222,32 L 222,40 Q 222,55 229,55 L 237,55" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="273,82 261,86 261,77" style="fill:rgb(0,0,0)"/>
<path d="M194,55 L 202,55 Q 209,55 209,68 Q 209,82 224,82 L 252,82 L 267,82" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M273,82 L 310,82 Q 325,82 325,68 Q 325,55 332,55 L 340,55" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M273,124A15 15 0 0 0 288 109A15 15 0 0 0 273 94A15 15 0 0 0 258 109A15 15 0 0 0 273 124Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="273" y="109" text-anchor="middle" font-weight="bold" fill="rgb(0,0,0)" dominant-baseline="central">*</text>
<polygon points="258,109 246,113 246,105" style="fill:rgb(0,0,0)"/>
<path d="M194,55 L 202,55 Q 209,55 209,70 L 209,94 Q 209,109 224,109 L 237,109 L 252,109" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M288,109 L 310,109 Q 325,109 325,94 L 325,85 L 325,70" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
</svg>
</div>
</div>
<p>
See the <a href="lang_expr.html#*funcinexpr">functions within expressions</a> documentation for
more information about how SQL function invocations fit into the context
of an SQL expression.
</p><h1 id="list_of_core_functions"><span>2. </span>List Of Core Functions</h1>
<p>
<div class='columns' style='columns: 15em auto;'>
<ul style='padding-top:0;'>
<li><a href='lang_corefunc.html#abs'>abs(X)</a></li>
<li><a href='lang_corefunc.html#changes'>changes()</a></li>
<li><a href='lang_corefunc.html#char'>char(X1,X2,...,XN)</a></li>
<li><a href='lang_corefunc.html#coalesce'>coalesce(X,Y,...)</a></li>
<li><a href='lang_corefunc.html#concat'>concat(X,...)</a></li>
<li><a href='lang_corefunc.html#concat_ws'>concat_ws(SEP,X,...)</a></li>
<li><a href='lang_corefunc.html#format'>format(FORMAT,...)</a></li>
<li><a href='lang_corefunc.html#glob'>glob(X,Y)</a></li>
<li><a href='lang_corefunc.html#hex'>hex(X)</a></li>
<li><a href='lang_corefunc.html#ifnull'>ifnull(X,Y)</a></li>
<li><a href='lang_corefunc.html#iif'>iif(X,Y,Z)</a></li>
<li><a href='lang_corefunc.html#instr'>instr(X,Y)</a></li>
<li><a href='lang_corefunc.html#last_insert_rowid'>last_insert_rowid()</a></li>
<li><a href='lang_corefunc.html#length'>length(X)</a></li>
<li><a href='lang_corefunc.html#like'>like(X,Y)</a></li>
<li><a href='lang_corefunc.html#like'>like(X,Y,Z)</a></li>
<li><a href='lang_corefunc.html#likelihood'>likelihood(X,Y)</a></li>
<li><a href='lang_corefunc.html#likely'>likely(X)</a></li>
<li><a href='lang_corefunc.html#load_extension'>load_extension(X)</a></li>
<li><a href='lang_corefunc.html#load_extension'>load_extension(X,Y)</a></li>
<li><a href='lang_corefunc.html#lower'>lower(X)</a></li>
<li><a href='lang_corefunc.html#ltrim'>ltrim(X)</a></li>
<li><a href='lang_corefunc.html#ltrim'>ltrim(X,Y)</a></li>
<li><a href='lang_corefunc.html#max_scalar'>max(X,Y,...)</a></li>
<li><a href='lang_corefunc.html#min_scalar'>min(X,Y,...)</a></li>
<li><a href='lang_corefunc.html#nullif'>nullif(X,Y)</a></li>
<li><a href='lang_corefunc.html#octet_length'>octet_length(X)</a></li>
<li><a href='lang_corefunc.html#printf'>printf(FORMAT,...)</a></li>
<li><a href='lang_corefunc.html#quote'>quote(X)</a></li>
<li><a href='lang_corefunc.html#random'>random()</a></li>
<li><a href='lang_corefunc.html#randomblob'>randomblob(N)</a></li>
<li><a href='lang_corefunc.html#replace'>replace(X,Y,Z)</a></li>
<li><a href='lang_corefunc.html#round'>round(X)</a></li>
<li><a href='lang_corefunc.html#round'>round(X,Y)</a></li>
<li><a href='lang_corefunc.html#rtrim'>rtrim(X)</a></li>
<li><a href='lang_corefunc.html#rtrim'>rtrim(X,Y)</a></li>
<li><a href='lang_corefunc.html#sign'>sign(X)</a></li>
<li><a href='lang_corefunc.html#soundex'>soundex(X)</a></li>
<li><a href='lang_corefunc.html#sqlite_compileoption_get'>sqlite_compileoption_get(N)</a></li>
<li><a href='lang_corefunc.html#sqlite_compileoption_used'>sqlite_compileoption_used(X)</a></li>
<li><a href='lang_corefunc.html#sqlite_offset'>sqlite_offset(X)</a></li>
<li><a href='lang_corefunc.html#sqlite_source_id'>sqlite_source_id()</a></li>
<li><a href='lang_corefunc.html#sqlite_version'>sqlite_version()</a></li>
<li><a href='lang_corefunc.html#substr'>substr(X,Y)</a></li>
<li><a href='lang_corefunc.html#substr'>substr(X,Y,Z)</a></li>
<li><a href='lang_corefunc.html#substr'>substring(X,Y)</a></li>
<li><a href='lang_corefunc.html#substr'>substring(X,Y,Z)</a></li>
<li><a href='lang_corefunc.html#total_changes'>total_changes()</a></li>
<li><a href='lang_corefunc.html#trim'>trim(X)</a></li>
<li><a href='lang_corefunc.html#trim'>trim(X,Y)</a></li>
<li><a href='lang_corefunc.html#typeof'>typeof(X)</a></li>
<li><a href='lang_corefunc.html#unhex'>unhex(X)</a></li>
<li><a href='lang_corefunc.html#unhex'>unhex(X,Y)</a></li>
<li><a href='lang_corefunc.html#unicode'>unicode(X)</a></li>
<li><a href='lang_corefunc.html#unlikely'>unlikely(X)</a></li>
<li><a href='lang_corefunc.html#upper'>upper(X)</a></li>
<li><a href='lang_corefunc.html#zeroblob'>zeroblob(N)</a></li>
</ul>
</div>
<h1 id="descriptions_of_built_in_scalar_sql_functions"><span>3. </span>Descriptions of built-in scalar SQL functions</h1>
<dl>
<a name="abs"></a>
<dt><p><b>abs(<i>X</i>)</b></dt><dd><p>
The abs(X) function returns the absolute value of the numeric
argument X. Abs(X) returns NULL if X is NULL.
Abs(X) returns 0.0 if X is a string or blob
that cannot be converted to a numeric value. If X is the
integer -9223372036854775808 then abs(X) throws an integer overflow
error since there is no equivalent positive 64-bit two complement value.
</dd>
<a name="changes"></a>
<dt><p><b>changes()</b></dt><dd><p>
The changes() function returns the number of database rows that were changed
or inserted or deleted by the most recently completed INSERT, DELETE,
or UPDATE statement, exclusive of statements in lower-level triggers.
The changes() SQL function is a wrapper around the <a href="c3ref/changes.html">sqlite3_changes64()</a>
C/C++ function and hence follows the same rules for counting changes.
</dd>
<a name="char"></a>
<dt><p><b>char(<i>X1</i>,<i>X2</i>,...,<i>XN</i>)</b></dt><dd><p>
The char(X1,X2,...,XN) function returns a string composed of characters having the
unicode code point values of integers X1 through XN, respectively.
</dd>
<a name="coalesce"></a>
<dt><p><b>coalesce(<i>X</i>,<i>Y</i>,...)</b></dt><dd><p>
The coalesce() function returns a copy of its first non-NULL argument, or
NULL if all arguments are NULL. Coalesce() must have at least
2 arguments.
</dd>
<a name="concat"></a>
<dt><p><b>concat(<i>X</i>,...)</b></dt><dd><p>
The concat(...) function returns a string which is the concatenation of the
string representation of all of its non-NULL arguments. If all arguments are
NULL, then concat() returns an empty string.
</dd>
<a name="concat_ws"></a>
<dt><p><b>concat_ws(<i>SEP</i>,<i>X</i>,...)</b></dt><dd><p>
The concat_ws(SEP,...) function returns a string that is the concatenation of
all non-null arguments beyond the first argument, using the text value of the
first argument as a separator. If the first argument is NULL, then concat_ws()
returns NULL. If all arguments other than the first are NULL, then concat_ws()
returns an empty string.
</dd>
<a name="format"></a>
<dt><p><b>format(<i>FORMAT</i>,...)</b></dt><dd><p>
The format(FORMAT,...) SQL function works like the <a href="c3ref/mprintf.html">sqlite3_mprintf()</a> C-language
function and the printf() function from the standard C library.
The first argument is a format string that specifies how to construct the output
string using values taken from subsequent arguments. If the FORMAT argument is
missing or NULL then the result is NULL. The %n format is silently ignored and
does not consume an argument. The %p format is an alias for %X. The %z format
is interchangeable with %s. If there are too few arguments in the argument list,
missing arguments are assumed to have a NULL value, which is translated into
0 or 0.0 for numeric formats or an empty string for %s. See the
<a href="printf.html">built-in printf()</a> documentation for additional information.
</dd>
<a name="glob"></a>
<dt><p><b>glob(<i>X</i>,<i>Y</i>)</b></dt><dd><p>
The glob(X,Y) function is equivalent to the
expression "<b>Y GLOB X</b>".
Note that the X and Y arguments are reversed in the glob() function
relative to the infix <a href="lang_expr.html#glob">GLOB</a> operator. Y is the string and X is the
pattern. So, for example, the following expressions are equivalent:
<blockquote><pre>
name GLOB '*helium*'
glob('*helium*',name)
</pre></blockquote>
<p>If the <a href="c3ref/create_function.html">sqlite3_create_function()</a> interface is used to
override the glob(X,Y) function with an alternative implementation then
the <a href="lang_expr.html#glob">GLOB</a> operator will invoke the alternative implementation.
</dd>
<a name="hex"></a>
<dt><p><b>hex(<i>X</i>)</b></dt><dd><p>
The hex() function interprets its argument as a BLOB and returns
a string which is the upper-case hexadecimal rendering of the content of
that blob.
<p>
If the argument <i>X</i> in "hex(<i>X</i>)" is an
integer or floating point number, then "interprets its argument as a BLOB" means
that the binary number is first converted into a UTF8 text representation, then
that text is interpreted as a BLOB. Hence, "hex(12345678)" renders
as "3132333435363738" not the binary representation of the integer value
"0000000000BC614E".
<p>
See also: <a href="lang_corefunc.html#unhex">unhex()</a>
</dd>
<a name="ifnull"></a>
<dt><p><b>ifnull(<i>X</i>,<i>Y</i>)</b></dt><dd><p>
The ifnull() function returns a copy of its first non-NULL argument, or
NULL if both arguments are NULL. Ifnull() must have exactly 2 arguments.
The ifnull() function is equivalent to <a href="lang_corefunc.html#coalesce">coalesce()</a> with two arguments.
</dd>
<a name="iif"></a>
<dt><p><b>iif(<i>X</i>,<i>Y</i>,<i>Z</i>)</b></dt><dd><p>
The iif(X,Y,Z) function returns the value Y if X is true, and Z otherwise.
The iif(X,Y,Z) function is logically equivalent to and generates the same
<a href="opcode.html">bytecode</a> as the <a href="lang_expr.html#case">CASE expression</a> "CASE WHEN X THEN Y ELSE Z END".
</dd>
<a name="instr"></a>
<dt><p><b>instr(<i>X</i>,<i>Y</i>)</b></dt><dd><p>
The instr(X,Y) function finds the first occurrence of string Y within
string X and returns the number of prior characters plus 1, or 0 if
Y is nowhere found within X.
Or, if X and Y are both BLOBs, then instr(X,Y) returns one
more than the number bytes prior to the first occurrence of Y, or 0 if
Y does not occur anywhere within X.
If both arguments X and Y to instr(X,Y) are non-NULL and are not BLOBs
then both are interpreted as strings.
If either X or Y are NULL in instr(X,Y) then the result is NULL.
</dd>
<a name="last_insert_rowid"></a>
<dt><p><b>last_insert_rowid()</b></dt><dd><p>
The last_insert_rowid() function returns the <a href="lang_createtable.html#rowid">ROWID</a>
of the last row insert from the database connection which invoked the
function.
The last_insert_rowid() SQL function is a wrapper around the
<a href="c3ref/last_insert_rowid.html">sqlite3_last_insert_rowid()</a> C/C++ interface function.
</dd>
<a name="length"></a>
<dt><p><b>length(<i>X</i>)</b></dt><dd><p>
For a string value X, the length(X) function returns the number of
characters (not bytes) in X prior to the first NUL character.
Since SQLite strings do not normally contain NUL characters, the length(X)
function will usually return the total number of characters in the string X.
For a blob value X, length(X) returns the number of bytes in the blob.
If X is NULL then length(X) is NULL.
If X is numeric then length(X) returns the length of a string
representation of X.
<p>
Note that for strings, the length(X) function returns the <i>character</i>
length of the string, not the byte length. The character length is the number
of characters in the string. The character length is always different from
the byte length for UTF-16 strings, and can be different from the byte length
for UTF-8 strings if the string contains multi-byte characters. Use the
<a href="lang_corefunc.html#octet_length">octet_length()</a> function to find the byte length of a string.
<p>
For BLOB values, length(X) always returns the byte-length of the BLOB.
<p>
For string values, length(X) must read the entire string into memory in order
to compute the character length. But for BLOB values, that is not necessary as
SQLite knows how many bytes are in the BLOB. Hence, for multi-megabyte values,
the length(X) function is usually much faster for BLOBs than for strings, since
it does not need to load the value into memory.
</dd>
<a name="like"></a>
<dt><p><b>like(<i>X</i>,<i>Y</i>)<br />like(<i>X</i>,<i>Y</i>,<i>Z</i>)</b></dt><dd><p>
The like() function is used to implement the
"<b>Y LIKE X [ESCAPE Z]</b>" expression.
If the optional ESCAPE clause is present, then the
like() function is invoked with three arguments. Otherwise, it is
invoked with two arguments only. Note that the X and Y parameters are
reversed in the like() function relative to the infix <a href="lang_expr.html#like">LIKE</a> operator.
X is the pattern and Y is the string to match against that pattern.
Hence, the following expressions are equivalent:
<blockquote><pre>
name LIKE '%neon%'
like('%neon%',name)
</pre></blockquote>
<p>The <a href="c3ref/create_function.html">sqlite3_create_function()</a> interface can be used to override the
like() function and thereby change the operation of the
<a href="lang_expr.html#like">LIKE</a> operator. When overriding the like() function, it may be important
to override both the two and three argument versions of the like()
function. Otherwise, different code may be called to implement the
<a href="lang_expr.html#like">LIKE</a> operator depending on whether or not an ESCAPE clause was
specified.
</dd>
<a name="likelihood"></a>
<dt><p><b>likelihood(<i>X</i>,<i>Y</i>)</b></dt><dd><p>
The likelihood(X,Y) function returns argument X unchanged.
The value Y in likelihood(X,Y) must be a floating point constant
between 0.0 and 1.0, inclusive.
The likelihood(X) function is a no-op that the code generator
optimizes away so that it consumes no CPU cycles during run-time
(that is, during calls to <a href="c3ref/step.html">sqlite3_step()</a>).
The purpose of the likelihood(X,Y) function is to provide a hint
to the query planner that the argument X is a boolean that is
true with a probability of approximately Y.
The <a href="lang_corefunc.html#unlikely">unlikely(X)</a> function is short-hand for likelihood(X,0.0625).
The <a href="lang_corefunc.html#likely">likely(X)</a> function is short-hand for likelihood(X,0.9375).
</dd>
<a name="likely"></a>
<dt><p><b>likely(<i>X</i>)</b></dt><dd><p>
The likely(X) function returns the argument X unchanged.
The likely(X) function is a no-op that the code generator
optimizes away so that it consumes no CPU cycles at
run-time (that is, during calls to <a href="c3ref/step.html">sqlite3_step()</a>).
The purpose of the likely(X) function is to provide a hint
to the query planner that the argument X is a boolean value
that is usually true. The likely(X) function is equivalent
to <a href="lang_corefunc.html#likelihood">likelihood</a>(X,0.9375). See also: <a href="lang_corefunc.html#unlikely">unlikely(X)</a>.
</dd>
<a name="load_extension"></a>
<dt><p><b>load_extension(<i>X</i>)<br />load_extension(<i>X</i>,<i>Y</i>)</b></dt><dd><p>
The load_extension(X,Y) function loads <a href="loadext.html">SQLite extensions</a> out of the shared
library file named X using the entry point Y. The result of load_extension()
is always a NULL. If Y is omitted then the default entry point name is used.
The load_extension() function raises an exception if the extension fails to
load or initialize correctly.
<p>The load_extension() function will fail if the extension attempts to
modify or delete an SQL function or collating sequence. The
extension can add new functions or collating sequences, but cannot
modify or delete existing functions or collating sequences because
those functions and/or collating sequences might be used elsewhere
in the currently running SQL statement. To load an extension that
changes or deletes functions or collating sequences, use the
<a href="c3ref/load_extension.html">sqlite3_load_extension()</a> C-language API.</p>
<p>For security reasons, extension loading is disabled by default and must
be enabled by a prior call to <a href="c3ref/enable_load_extension.html">sqlite3_enable_load_extension()</a>.</p>
</dd>
<a name="lower"></a>
<dt><p><b>lower(<i>X</i>)</b></dt><dd><p>
The lower(X) function returns a copy of string X with all ASCII characters
converted to lower case. The default built-in lower() function works
for ASCII characters only. To do case conversions on non-ASCII
characters, load the ICU extension.
</dd>
<a name="ltrim"></a>
<dt><p><b>ltrim(<i>X</i>)<br />ltrim(<i>X</i>,<i>Y</i>)</b></dt><dd><p>
The ltrim(X,Y) function returns a string formed by removing any and all
characters that appear in Y from the left side of X.
If the Y argument is omitted, ltrim(X) removes spaces from the left side
of X.
</dd>
<a name="max_scalar"></a>
<dt><p><b>max(<i>X</i>,<i>Y</i>,...)</b></dt><dd><p>
The multi-argument max() function returns the argument with the
maximum value, or return NULL if any argument is NULL.
The multi-argument max() function searches its arguments from left to right
for an argument that defines a collating function and uses that collating
function for all string comparisons. If none of the arguments to max()
define a collating function, then the BINARY collating function is used.
Note that <b>max()</b> is a simple function when
it has 2 or more arguments but operates as an
<a href="lang_aggfunc.html#max_agg">aggregate function</a> if given only a single argument.
</dd>
<a name="min_scalar"></a>
<dt><p><b>min(<i>X</i>,<i>Y</i>,...)</b></dt><dd><p>
The multi-argument min() function returns the argument with the
minimum value.
The multi-argument min() function searches its arguments from left to right
for an argument that defines a collating function and uses that collating
function for all string comparisons. If none of the arguments to min()
define a collating function, then the BINARY collating function is used.
Note that <b>min()</b> is a simple function when
it has 2 or more arguments but operates as an
<a href="lang_aggfunc.html#min_agg">aggregate function</a> if given
only a single argument.
</dd>
<a name="nullif"></a>
<dt><p><b>nullif(<i>X</i>,<i>Y</i>)</b></dt><dd><p>
The nullif(X,Y) function returns its first argument if the arguments are
different and NULL if the arguments are the same. The nullif(X,Y) function
searches its arguments from left to right for an argument that defines a
collating function and uses that collating function for all string
comparisons. If neither argument to nullif() defines a collating function
then the BINARY collating function is used.
</dd>
<a name="octet_length"></a>
<dt><p><b>octet_length(<i>X</i>)</b></dt><dd><p>
The octet_length(X) function returns the number of bytes in the encoding
of text string X. If X is NULL then octet_length(X) returns NULL. If X is
a BLOB value, then octet_length(X) is the same as <a href="lang_corefunc.html#length">length(X)</a>. If X is a
numeric value, then octet_length(X) returns the number of bytes in a text
rendering of that number.
<p>
Because octet_length(X) returns the number of bytes in X, not the number
of characters, the value returned depends on the database encoding. The
octet_length() function can return different answers for the same input string
if the database encoding is UTF16 instead of UTF8.
<p>
If argument X is a table column and the value is of type text or blob,
then octet_length(X) avoids reading the content of X from disk, as the byte
length can be computed from metadata. Thus, octet_length(X) is efficient
even if X is a column containing a multi-megabyte text or blob value.
</dd>
<a name="printf"></a>
<dt><p><b>printf(<i>FORMAT</i>,...)</b></dt><dd><p>
The printf() SQL function is an alias for the <a href="lang_corefunc.html#format">format() SQL function</a>.
The format() SQL function was originally named printf(). But the name was
later changed to format() for compatibility with other database engines.
The printf() name is retained as an alias so as not to break legacy code.
</dd>
<a name="quote"></a>
<dt><p><b>quote(<i>X</i>)</b></dt><dd><p>
The quote(X) function returns the text of an SQL literal which
is the value of its argument suitable for inclusion into an SQL statement.
Strings are surrounded by single-quotes with escapes on interior quotes
as needed. BLOBs are encoded as hexadecimal literals.
Strings with embedded NUL characters cannot be represented as string
literals in SQL and hence the returned string literal is truncated prior
to the first NUL.
</dd>
<a name="random"></a>
<dt><p><b>random()</b></dt><dd><p>
The random() function returns a pseudo-random integer
between -9223372036854775808 and +9223372036854775807.
</dd>
<a name="randomblob"></a>
<dt><p><b>randomblob(<i>N</i>)</b></dt><dd><p>
The randomblob(N) function return an N-byte blob containing pseudo-random
bytes. If N is less than 1 then a 1-byte random blob is returned.
<p>Hint: applications can generate globally unique identifiers
using this function together with <a href="lang_corefunc.html#hex">hex()</a> and/or
<a href="lang_corefunc.html#lower">lower()</a> like this:</p>
<blockquote>
hex(randomblob(16))<br></br>
lower(hex(randomblob(16)))
</blockquote>
</dd>
<a name="replace"></a>
<dt><p><b>replace(<i>X</i>,<i>Y</i>,<i>Z</i>)</b></dt><dd><p>
The replace(X,Y,Z) function returns a string formed by substituting
string Z for every occurrence of string Y in string X. The <a href="datatype3.html#collation">BINARY</a>
collating sequence is used for comparisons. If Y is an empty
string then return X unchanged. If Z is not initially
a string, it is cast to a UTF-8 string prior to processing.
</dd>
<a name="round"></a>
<dt><p><b>round(<i>X</i>)<br />round(<i>X</i>,<i>Y</i>)</b></dt><dd><p>
The round(X,Y) function returns a floating-point
value X rounded to Y digits to the right of the decimal point.
If the Y argument is omitted or negative, it is taken to be 0.
</dd>
<a name="rtrim"></a>
<dt><p><b>rtrim(<i>X</i>)<br />rtrim(<i>X</i>,<i>Y</i>)</b></dt><dd><p>
The rtrim(X,Y) function returns a string formed by removing any and all
characters that appear in Y from the right side of X.
If the Y argument is omitted, rtrim(X) removes spaces from the right
side of X.
</dd>
<a name="sign"></a>
<dt><p><b>sign(<i>X</i>)</b></dt><dd><p>
The sign(X) function returns -1, 0, or +1 if the argument X is a numeric
value that is negative, zero, or positive, respectively. If the argument
to sign(X) is NULL or is a string or blob that cannot be losslessly converted
into a number, then sign(X) returns NULL.
</dd>
<a name="soundex"></a>
<dt><p><b>soundex(<i>X</i>)</b></dt><dd><p>
The soundex(X) function returns a string that is the soundex encoding
of the string X.
The string "?000" is returned if the argument is NULL or contains
no ASCII alphabetic characters.
This function is omitted from SQLite by default.
It is only available if the <a href="compile.html#soundex">SQLITE_SOUNDEX</a> compile-time option
is used when SQLite is built.
</dd>
<a name="sqlite_compileoption_get"></a>
<dt><p><b>sqlite_compileoption_get(<i>N</i>)</b></dt><dd><p>
The sqlite_compileoption_get() SQL function is a wrapper around the
<a href="c3ref/compileoption_get.html">sqlite3_compileoption_get()</a> C/C++ function.
This routine returns the N-th compile-time option used to build SQLite
or NULL if N is out of range. See also the <a href="pragma.html#pragma_compile_options">compile_options pragma</a>.
</dd>
<a name="sqlite_compileoption_used"></a>
<dt><p><b>sqlite_compileoption_used(<i>X</i>)</b></dt><dd><p>
The sqlite_compileoption_used() SQL function is a wrapper around the
<a href="c3ref/compileoption_get.html">sqlite3_compileoption_used()</a> C/C++ function.
When the argument X to sqlite_compileoption_used(X) is a string which
is the name of a compile-time option, this routine returns true (1) or
false (0) depending on whether or not that option was used during the
build.
</dd>
<a name="sqlite_offset"></a>
<dt><p><b>sqlite_offset(<i>X</i>)</b></dt><dd><p>
The sqlite_offset(X) function returns the byte offset in the database
file for the beginning of the record from which value would be read.
If X is not a column in an ordinary table, then sqlite_offset(X) returns
NULL. The value returned by sqlite_offset(X) might reference either the
original table or an index, depending on the query. If the value X would
normally be extracted from an index, the sqlite_offset(X) returns the
offset to the corresponding index record. If the value X would be
extracted from the original table, then sqlite_offset(X) returns the offset
to the table record.
<p>The sqlite_offset(X) SQL function is only available if SQLite is built
using the <a href="compile.html#enable_offset_sql_func">-DSQLITE_ENABLE_OFFSET_SQL_FUNC</a> compile-time option.
</dd>
<a name="sqlite_source_id"></a>
<dt><p><b>sqlite_source_id()</b></dt><dd><p>
The sqlite_source_id() function returns a string that identifies the
specific version of the source code that was used to build the SQLite
library. The string returned by sqlite_source_id() is
the date and time that the source code was checked in followed by
the SHA3-256 hash for that check-in. This function is
an SQL wrapper around the <a href="c3ref/libversion.html">sqlite3_sourceid()</a> C interface.
</dd>
<a name="sqlite_version"></a>
<dt><p><b>sqlite_version()</b></dt><dd><p>
The sqlite_version() function returns the version string for the SQLite
library that is running. This function is an SQL
wrapper around the <a href="c3ref/libversion.html">sqlite3_libversion()</a> C-interface.
</dd>
<a name="substr"></a>
<dt><p><b>substr(<i>X</i>,<i>Y</i>,<i>Z</i>)<br />substr(<i>X</i>,<i>Y</i>)<br />substring(<i>X</i>,<i>Y</i>,<i>Z</i>)<br />substring(<i>X</i>,<i>Y</i>)</b></dt><dd><p>
The substr(X,Y,Z) function returns a substring of input string X that begins
with the Y-th character and which is Z characters long.
If Z is omitted then substr(X,Y) returns all characters through the end
of the string X beginning with the Y-th.
The left-most character of X is number 1. If Y is negative
then the first character of the substring is found by counting from the
right rather than the left. If Z is negative then
the abs(Z) characters preceding the Y-th character are returned.
If X is a string then characters indices refer to actual UTF-8
characters. If X is a BLOB then the indices refer to bytes.
<p>
"substring()" is an alias for "substr()" beginning with SQLite version 3.34.
</dd>
<a name="total_changes"></a>
<dt><p><b>total_changes()</b></dt><dd><p>
The total_changes() function returns the number of row changes
caused by INSERT, UPDATE or DELETE
statements since the current database connection was opened.
This function is a wrapper around the <a href="c3ref/total_changes.html">sqlite3_total_changes64()</a>
C/C++ interface.
</dd>
<a name="trim"></a>
<dt><p><b>trim(<i>X</i>)<br />trim(<i>X</i>,<i>Y</i>)</b></dt><dd><p>
The trim(X,Y) function returns a string formed by removing any and all
characters that appear in Y from both ends of X.
If the Y argument is omitted, trim(X) removes spaces from both ends of X.
</dd>
<a name="typeof"></a>
<dt><p><b>typeof(<i>X</i>)</b></dt><dd><p>
The typeof(X) function returns a string that indicates the <a href="datatype3.html">datatype</a> of
the expression X: "null", "integer", "real", "text", or "blob".
</dd>
<a name="unhex"></a>
<dt><p><b>unhex(<i>X</i>)<br />unhex(<i>X</i>,<i>Y</i>)</b></dt><dd><p>
The unhex(X,Y) function returns a BLOB value which is the decoding of the
hexadecimal string X. If X contains any
characters that are not hexadecimal digits and which are not in Y,
then unhex(X,Y) returns NULL. If Y is omitted, it is
understood to be an empty string and hence X must be a pure hexadecimal string.
All hexadecimal digits in X must occur in pairs, with both digits of each
pair beginning immediately adjacent to one another, or else unhex(X,Y)
returns NULL.
If either parameter X or Y is NULL, then unhex(X,Y) returns NULL.
The X input may contain an arbitrary mix of upper and lower case hexadecimal
digits.
Hexadecimal digits in Y have no affect on the translation of X. Only
characters in Y that are not hexadecimal digits are ignored in X.
<p>
See also: <a href="lang_corefunc.html#hex">hex()</a>
</dd>
<a name="unicode"></a>
<dt><p><b>unicode(<i>X</i>)</b></dt><dd><p>
The unicode(X) function returns the numeric unicode code point corresponding to
the first character of the string X. If the argument to unicode(X) is not a string
then the result is undefined.
</dd>
<a name="unlikely"></a>
<dt><p><b>unlikely(<i>X</i>)</b></dt><dd><p>
The unlikely(X) function returns the argument X unchanged.
The unlikely(X) function is a no-op that the code generator
optimizes away so that it consumes no CPU cycles at
run-time (that is, during calls to <a href="c3ref/step.html">sqlite3_step()</a>).
The purpose of the unlikely(X) function is to provide a hint
to the query planner that the argument X is a boolean value
that is usually not true. The unlikely(X) function is equivalent
to <a href="lang_corefunc.html#likelihood">likelihood</a>(X, 0.0625).
</dd>
<a name="upper"></a>
<dt><p><b>upper(<i>X</i>)</b></dt><dd><p>
The upper(X) function returns a copy of input string X in which all
lower-case ASCII characters are converted to their upper-case equivalent.
</dd>
<a name="zeroblob"></a>
<dt><p><b>zeroblob(<i>N</i>)</b></dt><dd><p>
The zeroblob(N) function returns a BLOB consisting of N bytes of 0x00.
SQLite manages these zeroblobs very efficiently. Zeroblobs can be used to
reserve space for a BLOB that is later written using
<a href="c3ref/blob_open.html">incremental BLOB I/O</a>.
This SQL function is implemented using the <a href="c3ref/result_blob.html">sqlite3_result_zeroblob()</a>
routine from the C/C++ interface.
</dd>
</dl>
<p align="center"><small><i>This page last modified on <a href="https://sqlite.org/docsrc/honeypot" id="mtimelink" data-href="https://sqlite.org/docsrc/finfo/pages/lang_corefunc.in?m=a451f26240">2023-12-05 14:43:20</a> UTC </small></i></p>
|
