Debian Codegraph @

www/lang_corefunc.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>Built-In Scalar SQL Functions</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 Built-In Scalar SQL Functions
122 </div>
123 </div>
124
125
126
127
128
129 <h1 id="overview"><span>1. </span>Overview</h1>
130
131 <p>The core functions shown below are available by default.
132 <a href="lang_datefunc.html">Date &amp; Time functions</a>,
133 <a href="lang_aggfunc.html">aggregate functions</a>,
134 <a href="windowfunctions.html">window functions</a>,
135 <a href="lang_mathfunc.html">math functions</a>, and
136 <a href="json1.html">JSON functions</a> are documented separately. An
137 application may define additional
138 functions written in C and added to the database engine using
139 the <a href="c3ref/create_function.html">sqlite3_create_function()</a> API.</p>
140
141 <p><b><a href="syntax/simple-function-invocation.html">simple-function-invocation:</a></b></p><div class='imgcontainer'>
142 <div style="max-width:414px"><svg xmlns='http://www.w3.org/2000/svg' style='font-size:initial;' class="pikchr" viewBox="0 0 414.49 126.792" data-pikchr-date="20250319161943">
143 <circle cx="5.76" cy="55.08" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
144 <polygon points="32.4,55.08 20.88,59.4 20.88,50.76" style="fill:rgb(0,0,0)"/>
145 <path d="M9.36,55.08L26.64,55.08" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
146 <path d="M47.52,70.2L126.259,70.2A15.12 15.12 0 0 0 141.379 55.08A15.12 15.12 0 0 0 126.259 39.96L47.52,39.96A15.12 15.12 0 0 0 32.4 55.08A15.12 15.12 0 0 0 47.52 70.2Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
147 <text x="86.8896" y="55.08" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">simple-func</text>
148 <polygon points="164.419,55.08 152.899,59.4 152.899,50.76" style="fill:rgb(0,0,0)"/>
149 <path d="M141.379,55.08L158.659,55.08" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
150 <path d="M179.539,70.2A15.12 15.12 0 0 0 194.659 55.08A15.12 15.12 0 0 0 179.539 39.96A15.12 15.12 0 0 0 164.419 55.08A15.12 15.12 0 0 0 179.539 70.2Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
151 <text x="179.539" y="55.08" text-anchor="middle" font-weight="bold" fill="rgb(0,0,0)" dominant-baseline="central">(</text>
152 <polygon points="248.659,55.08 237.139,59.4 237.139,50.76" style="fill:rgb(0,0,0)"/>
153 <path d="M194.659,55.08L242.899,55.08" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
154 <path d="M248.659,70.2L297.85,70.2L297.85,39.96L248.659,39.96Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
155 <text x="273.254" y="55.08" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">expr</text>
156 <polygon points="351.85,55.08 340.33,59.4 340.33,50.76" style="fill:rgb(0,0,0)"/>
157 <path d="M297.85,55.08L346.09,55.08" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
158 <path d="M366.97,70.2A15.12 15.12 0 0 0 382.09 55.08A15.12 15.12 0 0 0 366.97 39.96A15.12 15.12 0 0 0 351.85 55.08A15.12 15.12 0 0 0 366.97 70.2Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
159 <text x="366.97" y="55.08" text-anchor="middle" font-weight="bold" fill="rgb(0,0,0)" dominant-baseline="central">)</text>
160 <polygon points="405.13,55.08 393.61,59.4 393.61,50.76" style="fill:rgb(0,0,0)"/>
161 <path d="M382.09,55.08L399.37,55.08" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
162 <circle cx="408.73" cy="55.08" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
163 <path d="M273.254,32.4A15.12 15.12 0 0 0 288.374 17.28L288.374,17.28A15.12 15.12 0 0 0 273.254 2.16A15.12 15.12 0 0 0 258.134 17.28L258.134,17.28A15.12 15.12 0 0 0 273.254 32.4Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
164 <text x="273.254" y="17.28" text-anchor="middle" font-weight="bold" fill="rgb(0,0,0)" dominant-baseline="central">,</text>
165 <polygon points="288.374,17.28 299.894,12.96 299.894,21.6" style="fill:rgb(0,0,0)"/>
166 <path d="M297.85,55.08 L 305.35,55.08 Q 312.85,55.08 312.85,40.08 L 312.85,32.28 Q 312.85,17.28 303.492,17.28 L 294.134,17.28" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
167 <path d="M258.134,17.28 L 237.139,17.28 Q 222.139,17.28 222.139,32.28 L 222.139,40.08 Q 222.139,55.08 229.639,55.08 L 237.139,55.08" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
168 <polygon points="273.254,82.296 261.734,86.616 261.734,77.976" style="fill:rgb(0,0,0)"/>
169 <path d="M194.659,55.08 L 202.159,55.08 Q 209.659,55.08 209.659,68.688 Q 209.659,82.296 224.659,82.296 L 252.494,82.296 L 267.494,82.296" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
170 <path d="M273.254,82.296 L 310.33,82.296 Q 325.33,82.296 325.33,68.688 Q 325.33,55.08 332.83,55.08 L 340.33,55.08" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
171 <path d="M273.254,124.632A15.12 15.12 0 0 0 288.374 109.512A15.12 15.12 0 0 0 273.254 94.392A15.12 15.12 0 0 0 258.134 109.512A15.12 15.12 0 0 0 273.254 124.632Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
172 <text x="273.254" y="109.512" text-anchor="middle" font-weight="bold" fill="rgb(0,0,0)" dominant-baseline="central">*</text>
173 <polygon points="258.134,109.512 246.614,113.832 246.614,105.192" style="fill:rgb(0,0,0)"/>
174 <path d="M194.659,55.08 L 202.159,55.08 Q 209.659,55.08 209.659,70.08 L 209.659,94.512 Q 209.659,109.512 224.659,109.512 L 237.374,109.512 L 252.374,109.512" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
175 <path d="M288.374,109.512 L 310.33,109.512 Q 325.33,109.512 325.33,94.512 L 325.33,85.2 L 325.33,70.2" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
176 </svg>
177 </div>
178 </div>
179
180
181 <p>
182 See the <a href="lang_expr.html#*funcinexpr">functions within expressions</a> documentation for
183 more information about how SQL function invocations fit into the context
184 of an SQL expression.
185
186 </p><h1 id="list_of_core_functions"><span>2. </span>List Of Core Functions</h1>
187
188 <p>
189 <div class='columns' style='columns: 15em auto;'>
190 <ul style='padding-top:0;'>
191 <li><a href='lang_corefunc.html#abs'>abs(X)</a></li>
192 <li><a href='lang_corefunc.html#changes'>changes()</a></li>
193 <li><a href='lang_corefunc.html#char'>char(X1,X2,...,XN)</a></li>
194 <li><a href='lang_corefunc.html#coalesce'>coalesce(X,Y,...)</a></li>
195 <li><a href='lang_corefunc.html#concat'>concat(X,...)</a></li>
196 <li><a href='lang_corefunc.html#concat_ws'>concat_ws(SEP,X,...)</a></li>
197 <li><a href='lang_corefunc.html#format'>format(FORMAT,...)</a></li>
198 <li><a href='lang_corefunc.html#glob'>glob(X,Y)</a></li>
199 <li><a href='lang_corefunc.html#hex'>hex(X)</a></li>
200 <li><a href='lang_corefunc.html#iif'>if(B1,V1,...)</a></li>
201 <li><a href='lang_corefunc.html#ifnull'>ifnull(X,Y)</a></li>
202 <li><a href='lang_corefunc.html#iif'>iif(B1,V1,...)</a></li>
203 <li><a href='lang_corefunc.html#instr'>instr(X,Y)</a></li>
204 <li><a href='lang_corefunc.html#last_insert_rowid'>last_insert_rowid()</a></li>
205 <li><a href='lang_corefunc.html#length'>length(X)</a></li>
206 <li><a href='lang_corefunc.html#like'>like(X,Y)</a></li>
207 <li><a href='lang_corefunc.html#like'>like(X,Y,Z)</a></li>
208 <li><a href='lang_corefunc.html#likelihood'>likelihood(X,Y)</a></li>
209 <li><a href='lang_corefunc.html#likely'>likely(X)</a></li>
210 <li><a href='lang_corefunc.html#load_extension'>load_extension(X)</a></li>
211 <li><a href='lang_corefunc.html#load_extension'>load_extension(X,Y)</a></li>
212 <li><a href='lang_corefunc.html#lower'>lower(X)</a></li>
213 <li><a href='lang_corefunc.html#ltrim'>ltrim(X)</a></li>
214 <li><a href='lang_corefunc.html#ltrim'>ltrim(X,Y)</a></li>
215 <li><a href='lang_corefunc.html#max_scalar'>max(X,Y,...)</a></li>
216 <li><a href='lang_corefunc.html#min_scalar'>min(X,Y,...)</a></li>
217 <li><a href='lang_corefunc.html#nullif'>nullif(X,Y)</a></li>
218 <li><a href='lang_corefunc.html#octet_length'>octet_length(X)</a></li>
219 <li><a href='lang_corefunc.html#printf'>printf(FORMAT,...)</a></li>
220 <li><a href='lang_corefunc.html#quote'>quote(X)</a></li>
221 <li><a href='lang_corefunc.html#random'>random()</a></li>
222 <li><a href='lang_corefunc.html#randomblob'>randomblob(N)</a></li>
223 <li><a href='lang_corefunc.html#replace'>replace(X,Y,Z)</a></li>
224 <li><a href='lang_corefunc.html#round'>round(X)</a></li>
225 <li><a href='lang_corefunc.html#round'>round(X,Y)</a></li>
226 <li><a href='lang_corefunc.html#rtrim'>rtrim(X)</a></li>
227 <li><a href='lang_corefunc.html#rtrim'>rtrim(X,Y)</a></li>
228 <li><a href='lang_corefunc.html#sign'>sign(X)</a></li>
229 <li><a href='lang_corefunc.html#soundex'>soundex(X)</a></li>
230 <li><a href='lang_corefunc.html#sqlite_compileoption_get'>sqlite_compileoption_get(N)</a></li>
231 <li><a href='lang_corefunc.html#sqlite_compileoption_used'>sqlite_compileoption_used(X)</a></li>
232 <li><a href='lang_corefunc.html#sqlite_offset'>sqlite_offset(X)</a></li>
233 <li><a href='lang_corefunc.html#sqlite_source_id'>sqlite_source_id()</a></li>
234 <li><a href='lang_corefunc.html#sqlite_version'>sqlite_version()</a></li>
235 <li><a href='lang_corefunc.html#substr'>substr(X,Y)</a></li>
236 <li><a href='lang_corefunc.html#substr'>substr(X,Y,Z)</a></li>
237 <li><a href='lang_corefunc.html#substr'>substring(X,Y)</a></li>
238 <li><a href='lang_corefunc.html#substr'>substring(X,Y,Z)</a></li>
239 <li><a href='lang_corefunc.html#total_changes'>total_changes()</a></li>
240 <li><a href='lang_corefunc.html#trim'>trim(X)</a></li>
241 <li><a href='lang_corefunc.html#trim'>trim(X,Y)</a></li>
242 <li><a href='lang_corefunc.html#typeof'>typeof(X)</a></li>
243 <li><a href='lang_corefunc.html#unhex'>unhex(X)</a></li>
244 <li><a href='lang_corefunc.html#unhex'>unhex(X,Y)</a></li>
245 <li><a href='lang_corefunc.html#unicode'>unicode(X)</a></li>
246 <li><a href='lang_corefunc.html#unistr'>unistr(X)</a></li>
247 <li><a href='lang_corefunc.html#unistr_quote'>unistr_quote(X)</a></li>
248 <li><a href='lang_corefunc.html#unlikely'>unlikely(X)</a></li>
249 <li><a href='lang_corefunc.html#upper'>upper(X)</a></li>
250 <li><a href='lang_corefunc.html#zeroblob'>zeroblob(N)</a></li>
251 </ul>
252 </div>
253
254
255 <h1 id="descriptions_of_built_in_scalar_sql_functions"><span>3. </span>Descriptions of built-in scalar SQL functions</h1>
256 <dl>
257
258 <a name="abs"></a>
259 <dt><p><b>abs(<i>X</i>)</b></dt><dd><p>
260 The abs(X) function returns the absolute value of the numeric
261 argument X. Abs(X) returns NULL if X is NULL.
262 Abs(X) returns 0.0 if X is a string or blob
263 that cannot be converted to a numeric value. If X is the
264 integer -9223372036854775808 then abs(X) throws an integer overflow
265 error since there is no equivalent positive 64-bit two's complement value.
266 </dd>
267 <a name="changes"></a>
268 <dt><p><b>changes()</b></dt><dd><p>
269 The changes() function returns the number of database rows that were changed
270 or inserted or deleted by the most recently completed INSERT, DELETE,
271 or UPDATE statement, exclusive of statements in lower-level triggers.
272 The changes() SQL function is a wrapper around the <a href="c3ref/changes.html">sqlite3_changes64()</a>
273 C/C++ function and hence follows the same rules for counting changes.
274 </dd>
275 <a name="char"></a>
276 <dt><p><b>char(<i>X1</i>,<i>X2</i>,...,<i>XN</i>)</b></dt><dd><p>
277 The char(X1,X2,...,XN) function returns a string composed of characters having the
278 unicode code point values of integers X1 through XN, respectively.
279 </dd>
280 <a name="coalesce"></a>
281 <dt><p><b>coalesce(<i>X</i>,<i>Y</i>,...)</b></dt><dd><p>
282 The coalesce() function returns a copy of its first non-NULL argument, or
283 NULL if all arguments are NULL. Coalesce() must have at least
284 2 arguments.
285 </dd>
286 <a name="concat"></a>
287 <dt><p><b>concat(<i>X</i>,...)</b></dt><dd><p>
288 The concat(...) function returns a string which is the concatenation of the
289 string representation of all of its non-NULL arguments. If all arguments are
290 NULL, then concat() returns an empty string.
291 </dd>
292 <a name="concat_ws"></a>
293 <dt><p><b>concat_ws(<i>SEP</i>,<i>X</i>,...)</b></dt><dd><p>
294 The concat_ws(SEP,...) function returns a string that is the concatenation of
295 all non-null arguments beyond the first argument, using the text value of the
296 first argument as a separator. If the first argument is NULL, then concat_ws()
297 returns NULL. If all arguments other than the first are NULL, then concat_ws()
298 returns an empty string.
299 </dd>
300 <a name="format"></a>
301 <dt><p><b>format(<i>FORMAT</i>,...)</b></dt><dd><p>
302 The format(FORMAT,...) SQL function works like the <a href="c3ref/mprintf.html">sqlite3_mprintf()</a> C-language
303 function and the printf() function from the standard C library.
304 The first argument is a format string that specifies how to construct the output
305 string using values taken from subsequent arguments. If the FORMAT argument is
306 missing or NULL then the result is NULL. The %n format is silently ignored and
307 does not consume an argument. The %p format is an alias for %X. The %z format
308 is interchangeable with %s. If there are too few arguments in the argument list,
309 missing arguments are assumed to have a NULL value, which is translated into
310 0 or 0.0 for numeric formats or an empty string for %s. See the
311 <a href="printf.html">built-in printf()</a> documentation for additional information.
312 </dd>
313 <a name="glob"></a>
314 <dt><p><b>glob(<i>X</i>,<i>Y</i>)</b></dt><dd><p>
315 The glob(X,Y) function is equivalent to the
316 expression "<b>Y GLOB X</b>".
317 Note that the X and Y arguments are reversed in the glob() function
318 relative to the infix <a href="lang_expr.html#glob">GLOB</a> operator. Y is the string and X is the
319 pattern. So, for example, the following expressions are equivalent:
320 <blockquote><pre>
321 name GLOB '*helium*'
322 glob('*helium*',name)
323 </pre></blockquote>
324 <p>If the <a href="c3ref/create_function.html">sqlite3_create_function()</a> interface is used to
325 override the glob(X,Y) function with an alternative implementation then
326 the <a href="lang_expr.html#glob">GLOB</a> operator will invoke the alternative implementation.
327 </dd>
328 <a name="hex"></a>
329 <dt><p><b>hex(<i>X</i>)</b></dt><dd><p>
330 The hex() function interprets its argument as a BLOB and returns
331 a string which is the upper-case hexadecimal rendering of the content of
332 that blob.
333 <p>
334 If the argument <i>X</i> in "hex(<i>X</i>)" is an
335 integer or floating point number, then "interprets its argument as a BLOB" means
336 that the binary number is first converted into a UTF8 text representation, then
337 that text is interpreted as a BLOB. Hence, "hex(12345678)" renders
338 as "3132333435363738" not the binary representation of the integer value
339 "0000000000BC614E".
340 <p>
341 See also: <a href="lang_corefunc.html#unhex">unhex()</a>
342 </dd>
343 <a name="ifnull"></a>
344 <dt><p><b>ifnull(<i>X</i>,<i>Y</i>)</b></dt><dd><p>
345 The ifnull() function returns a copy of its first non-NULL argument, or
346 NULL if both arguments are NULL. Ifnull() must have exactly 2 arguments.
347 The ifnull() function is equivalent to <a href="lang_corefunc.html#coalesce">coalesce()</a> with two arguments.
348 </dd>
349 <a name="iif"></a>
350 <dt><p><b>iif(<i>B1</i>,<i>V1</i>,...)<br />if(<i>B1</i>,<i>V1</i>,...)</b></dt><dd><p>
351 The iif(B1,V1,...,BN,VN) function takes arguments in pairs. The first argument of each
352 pair is a Boolean and the second argument is a value to return if the Boolean is true.
353 The iif() function returns the value associated with the first true Boolean.
354 If the number of arguments to iif() is odd, then the last argument is a value that
355 returned if all prior Boolean arguments are false.
356 If the number of arguments is even and all Boolean arguments are false, then NULL
357 is returned.
358 The iif() function requires at least two arguments.
359 The iif() function is really a short-hand notation for a <a href="lang_expr.html#case">CASE expression</a>.
360 For example,
361 the iif(X,Y,Z) function is logically equivalent to and generates the same
362 <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".
363 The if() function is just an alternative spelling for iif().
364 <p>The iif() function uses short-circuit evaluation. Arguments are only
365 evaluated if necessary to compute the final result. So, for example, if one
366 of the value arguments involves an expensive computation (such as an elaborate
367 subquery) but the corresponding Boolean is false, the expensive computation never
368 occurs. Similarly, Boolean arguments past the first one that is true are never
369 evaluated.
370 <p>The iif() function originally required exactly three arguments.
371 The two-argument version of iif() and the ability to spell the function as "if()"
372 were features added in SQLite version 3.48.0 (2025-01-14)
373 The ability to accept more than 3 arguments was added in
374 SQLite version 3.49.0 (2025-02-06).
375 </dd>
376 <a name="instr"></a>
377 <dt><p><b>instr(<i>X</i>,<i>Y</i>)</b></dt><dd><p>
378 The instr(X,Y) function finds the first occurrence of string Y within
379 string X and returns the number of prior characters plus 1, or 0 if
380 Y is nowhere found within X.
381 Or, if X and Y are both BLOBs, then instr(X,Y) returns one
382 more than the number of bytes prior to the first occurrence of Y, or 0 if
383 Y does not occur anywhere within X.
384 If both arguments X and Y to instr(X,Y) are non-NULL and are not BLOBs
385 then both are interpreted as strings.
386 If either X or Y are NULL in instr(X,Y) then the result is NULL.
387 </dd>
388 <a name="last_insert_rowid"></a>
389 <dt><p><b>last_insert_rowid()</b></dt><dd><p>
390 The last_insert_rowid() function returns the <a href="lang_createtable.html#rowid">ROWID</a>
391 of the last row insert from the database connection which invoked the
392 function.
393 The last_insert_rowid() SQL function is a wrapper around the
394 <a href="c3ref/last_insert_rowid.html">sqlite3_last_insert_rowid()</a> C/C++ interface function.
395 </dd>
396 <a name="length"></a>
397 <dt><p><b>length(<i>X</i>)</b></dt><dd><p>
398 For a string value X, the length(X) function returns the number of
399 Unicode code points (not bytes) in input string X prior to the first U+0000
400 character.
401 Since SQLite strings do not normally contain NUL characters, the length(X)
402 function will usually return the total number of characters in the string X.
403 For a blob value X, length(X) returns the number of bytes in the blob.
404 If X is NULL then length(X) is NULL.
405 If X is numeric then length(X) returns the length of a string
406 representation of X.
407 <p>
408 Note that for strings, the length(X) function returns the <i>character</i>
409 or <i>code-point</i>
410 length of the string, not the byte length. The character length is the number
411 of characters in the string. The character length is always different from
412 the byte length for UTF-16 strings, and can be different from the byte length
413 for UTF-8 strings if the string contains multi-byte characters. Use the
414 <a href="lang_corefunc.html#octet_length">octet_length()</a> function to find the byte length of a string.
415 <p>
416 For BLOB values, length(X) always returns the byte-length of the BLOB.
417 <p>
418 For string values, length(X) must read the entire string into memory in order
419 to compute the character length. But for BLOB values, reading the whole string
420 into memory is not necessary as SQLite already
421 knows how many bytes are in the BLOB. Hence, for multi-megabyte values,
422 the length(X) function is usually much faster for BLOBs than for strings, since
423 it does not need to load the value into memory.
424 </dd>
425 <a name="like"></a>
426 <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>
427 The like() function is used to implement the
428 "<b>Y LIKE X &#91;ESCAPE Z&#93;</b>" expression.
429 If the optional ESCAPE clause is present, then the
430 like() function is invoked with three arguments. Otherwise, it is
431 invoked with two arguments only. Note that the X and Y parameters are
432 reversed in the like() function relative to the infix <a href="lang_expr.html#like">LIKE</a> operator.
433 X is the pattern and Y is the string to match against that pattern.
434 Hence, the following expressions are equivalent:
435 <blockquote><pre>
436 name LIKE '%neon%'
437 like('%neon%',name)
438 </pre></blockquote>
439 <p>The <a href="c3ref/create_function.html">sqlite3_create_function()</a> interface can be used to override the
440 like() function and thereby change the operation of the
441 <a href="lang_expr.html#like">LIKE</a> operator. When overriding the like() function, it may be important
442 to override both the two and three argument versions of the like()
443 function. Otherwise, different code may be called to implement the
444 <a href="lang_expr.html#like">LIKE</a> operator depending on whether or not an ESCAPE clause was
445 specified.
446 </dd>
447 <a name="likelihood"></a>
448 <dt><p><b>likelihood(<i>X</i>,<i>Y</i>)</b></dt><dd><p>
449 The likelihood(X,Y) function returns argument X unchanged.
450 The value Y in likelihood(X,Y) must be a floating point constant
451 between 0.0 and 1.0, inclusive.
452 The likelihood(X) function is a no-op that the code generator
453 optimizes away so that it consumes no CPU cycles during run-time
454 (that is, during calls to <a href="c3ref/step.html">sqlite3_step()</a>).
455 The purpose of the likelihood(X,Y) function is to provide a hint
456 to the query planner that the argument X is a boolean that is
457 true with a probability of approximately Y.
458 The <a href="lang_corefunc.html#unlikely">unlikely(X)</a> function is short-hand for likelihood(X,0.0625).
459 The <a href="lang_corefunc.html#likely">likely(X)</a> function is short-hand for likelihood(X,0.9375).
460 </dd>
461 <a name="likely"></a>
462 <dt><p><b>likely(<i>X</i>)</b></dt><dd><p>
463 The likely(X) function returns the argument X unchanged.
464 The likely(X) function is a no-op that the code generator
465 optimizes away so that it consumes no CPU cycles at
466 run-time (that is, during calls to <a href="c3ref/step.html">sqlite3_step()</a>).
467 The purpose of the likely(X) function is to provide a hint
468 to the query planner that the argument X is a boolean value
469 that is usually true. The likely(X) function is equivalent
470 to <a href="lang_corefunc.html#likelihood">likelihood</a>(X,0.9375). See also: <a href="lang_corefunc.html#unlikely">unlikely(X)</a>.
471 </dd>
472 <a name="load_extension"></a>
473 <dt><p><b>load_extension(<i>X</i>)<br />load_extension(<i>X</i>,<i>Y</i>)</b></dt><dd><p>
474 The load_extension(X,Y) function loads <a href="loadext.html">SQLite extensions</a> out of the shared
475 library file named X using the entry point Y. The result of load_extension()
476 is always a NULL. If Y is omitted then the default entry point name is used.
477 The load_extension() function raises an exception if the extension fails to
478 load or initialize correctly.
479
480 <p>The load_extension() function will fail if the extension attempts to
481 modify or delete an SQL function or collating sequence. The
482 extension can add new functions or collating sequences, but cannot
483 modify or delete existing functions or collating sequences because
484 those functions and/or collating sequences might be used elsewhere
485 in the currently running SQL statement. To load an extension that
486 changes or deletes functions or collating sequences, use the
487 <a href="c3ref/load_extension.html">sqlite3_load_extension()</a> C-language API.</p>
488
489 <p>For security reasons, extension loading is disabled by default and must
490 be enabled by a prior call to <a href="c3ref/enable_load_extension.html">sqlite3_enable_load_extension()</a>.</p>
491 </dd>
492 <a name="lower"></a>
493 <dt><p><b>lower(<i>X</i>)</b></dt><dd><p>
494 The lower(X) function returns a copy of string X with all ASCII characters
495 converted to lower case. The default built-in lower() function works
496 for ASCII characters only. To do case conversions on non-ASCII
497 characters, load the ICU extension.
498 </dd>
499 <a name="ltrim"></a>
500 <dt><p><b>ltrim(<i>X</i>)<br />ltrim(<i>X</i>,<i>Y</i>)</b></dt><dd><p>
501 The ltrim(X,Y) function returns a string formed by removing any and all
502 characters that appear in Y from the left side of X.
503 If the Y argument is omitted, ltrim(X) removes spaces from the left side
504 of X.
505 </dd>
506 <a name="max_scalar"></a>
507 <dt><p><b>max(<i>X</i>,<i>Y</i>,...)</b></dt><dd><p>
508 The multi-argument max() function returns the argument with the
509 maximum value, or return NULL if any argument is NULL.
510 The multi-argument max() function searches its arguments from left to right
511 for an argument that defines a collating function and uses that collating
512 function for all string comparisons. If none of the arguments to max()
513 define a collating function, then the BINARY collating function is used.
514 Note that <b>max()</b> is a simple function when
515 it has 2 or more arguments but operates as an
516 <a href="lang_aggfunc.html#max_agg">aggregate function</a> if given only a single argument.
517 </dd>
518 <a name="min_scalar"></a>
519 <dt><p><b>min(<i>X</i>,<i>Y</i>,...)</b></dt><dd><p>
520 The multi-argument min() function returns the argument with the
521 minimum value.
522 The multi-argument min() function searches its arguments from left to right
523 for an argument that defines a collating function and uses that collating
524 function for all string comparisons. If none of the arguments to min()
525 define a collating function, then the BINARY collating function is used.
526 Note that <b>min()</b> is a simple function when
527 it has 2 or more arguments but operates as an
528 <a href="lang_aggfunc.html#min_agg">aggregate function</a> if given
529 only a single argument.
530 </dd>
531 <a name="nullif"></a>
532 <dt><p><b>nullif(<i>X</i>,<i>Y</i>)</b></dt><dd><p>
533 The nullif(X,Y) function returns its first argument if the arguments are
534 different and NULL if the arguments are the same. The nullif(X,Y) function
535 searches its arguments from left to right for an argument that defines a
536 collating function and uses that collating function for all string
537 comparisons. If neither argument to nullif() defines a collating function
538 then the BINARY collating function is used.
539 </dd>
540 <a name="octet_length"></a>
541 <dt><p><b>octet_length(<i>X</i>)</b></dt><dd><p>
542 The octet_length(X) function returns the number of bytes in the encoding
543 of text string X. If X is NULL then octet_length(X) returns NULL. If X is
544 a BLOB value, then octet_length(X) is the same as <a href="lang_corefunc.html#length">length(X)</a>. If X is a
545 numeric value, then octet_length(X) returns the number of bytes in a text
546 rendering of that number.
547 <p>
548 Because octet_length(X) returns the number of bytes in X, not the number
549 of characters or code-points, the value returned depends on the database
550 encoding. The
551 octet_length() function can return different answers for the same input string
552 if the database encoding is UTF16 instead of UTF8.
553 <p>
554 If argument X is a table column and the value is of type text or blob,
555 then octet_length(X) avoids reading the content of X from disk, as the byte
556 length can be computed from metadata. Thus, octet_length(X) is efficient
557 even if X is a column containing a multi-megabyte text or blob value.
558 </dd>
559 <a name="printf"></a>
560 <dt><p><b>printf(<i>FORMAT</i>,...)</b></dt><dd><p>
561 The printf() SQL function is an alias for the <a href="lang_corefunc.html#format">format() SQL function</a>.
562 The format() SQL function was originally named printf(). But the name was
563 later changed to format() for compatibility with other database engines.
564 The printf() name is retained as an alias so as not to break legacy code.
565 </dd>
566 <a name="quote"></a>
567 <dt><p><b>quote(<i>X</i>)</b></dt><dd><p>
568 The quote(X) function returns the text of an SQL literal which
569 is the value of its argument suitable for inclusion into an SQL statement.
570 Strings are surrounded by single-quotes with escapes on interior quotes
571 as needed. BLOBs are encoded as hexadecimal literals.
572 Strings with embedded NUL characters cannot be represented as string
573 literals in SQL and hence the returned string literal is truncated prior
574 to the first NUL.
575 <p>
576 The <a href="lang_corefunc.html#unistr_quote">unistr_quote(X)</a> function works like quote(X) except that it
577 also escapes control characters.
578 </dd>
579 <a name="random"></a>
580 <dt><p><b>random()</b></dt><dd><p>
581 The random() function returns a pseudo-random integer
582 between -9223372036854775807 and +9223372036854775807.
583 <p>
584 The random() function deliberately avoids generating
585 -9223372036854775808 so that its value can always be passed
586 to <a href="lang_corefunc.html#abs">abs()</a>. The random() function has behaved this way
587 since version 3.3.5 (circa 2006) but this documentation
588 incorrectly stated that -9223372036854775808 was a possible
589 result until 2025-07-25.
590 </dd>
591 <a name="randomblob"></a>
592 <dt><p><b>randomblob(<i>N</i>)</b></dt><dd><p>
593 The randomblob(N) function return an N-byte blob containing pseudo-random
594 bytes. If N is less than 1 then a 1-byte random blob is returned.
595
596 <p>Hint: applications can generate globally unique identifiers
597 using this function together with <a href="lang_corefunc.html#hex">hex()</a> and/or
598 <a href="lang_corefunc.html#lower">lower()</a> like this:</p>
599
600 <blockquote>
601 hex(randomblob(16))<br></br>
602 lower(hex(randomblob(16)))
603 </blockquote>
604 </dd>
605 <a name="replace"></a>
606 <dt><p><b>replace(<i>X</i>,<i>Y</i>,<i>Z</i>)</b></dt><dd><p>
607 The replace(X,Y,Z) function returns a string formed by substituting
608 string Z for every occurrence of string Y in string X. The <a href="datatype3.html#collation">BINARY</a>
609 collating sequence is used for comparisons. If Y is an empty
610 string then return X unchanged. If Z is not initially
611 a string, it is cast to a UTF-8 string prior to processing.
612 </dd>
613 <a name="round"></a>
614 <dt><p><b>round(<i>X</i>)<br />round(<i>X</i>,<i>Y</i>)</b></dt><dd><p>
615 The round(X,Y) function returns a floating-point
616 value X rounded to Y digits to the right of the decimal point.
617 If the Y argument is omitted or negative, it is taken to be 0.
618 </dd>
619 <a name="rtrim"></a>
620 <dt><p><b>rtrim(<i>X</i>)<br />rtrim(<i>X</i>,<i>Y</i>)</b></dt><dd><p>
621 The rtrim(X,Y) function returns a string formed by removing any and all
622 characters that appear in Y from the right side of X.
623 If the Y argument is omitted, rtrim(X) removes spaces from the right
624 side of X.
625 </dd>
626 <a name="sign"></a>
627 <dt><p><b>sign(<i>X</i>)</b></dt><dd><p>
628 The sign(X) function returns -1, 0, or +1 if the argument X is a numeric
629 value that is negative, zero, or positive, respectively. If the argument
630 to sign(X) is NULL or a BLOB or a string that cannot be losslessly converted
631 into a number, then sign(X) returns NULL.
632 </dd>
633 <a name="soundex"></a>
634 <dt><p><b>soundex(<i>X</i>)</b></dt><dd><p>
635 The soundex(X) function returns a string that is the soundex encoding
636 of the string X.
637 The string "?000" is returned if the argument is NULL or contains
638 no ASCII alphabetic characters.
639 This function is omitted from SQLite by default.
640 It is only available if the <a href="compile.html#soundex">SQLITE_SOUNDEX</a> compile-time option
641 is used when SQLite is built.
642 </dd>
643 <a name="sqlite_compileoption_get"></a>
644 <dt><p><b>sqlite_compileoption_get(<i>N</i>)</b></dt><dd><p>
645 The sqlite_compileoption_get() SQL function is a wrapper around the
646 <a href="c3ref/compileoption_get.html">sqlite3_compileoption_get()</a> C/C++ function.
647 This routine returns the N-th compile-time option used to build SQLite
648 or NULL if N is out of range. See also the <a href="pragma.html#pragma_compile_options">compile_options pragma</a>.
649 </dd>
650 <a name="sqlite_compileoption_used"></a>
651 <dt><p><b>sqlite_compileoption_used(<i>X</i>)</b></dt><dd><p>
652 The sqlite_compileoption_used() SQL function is a wrapper around the
653 <a href="c3ref/compileoption_get.html">sqlite3_compileoption_used()</a> C/C++ function.
654 When the argument X to sqlite_compileoption_used(X) is a string which
655 is the name of a compile-time option, this routine returns true (1) or
656 false (0) depending on whether or not that option was used during the
657 build.
658 </dd>
659 <a name="sqlite_offset"></a>
660 <dt><p><b>sqlite_offset(<i>X</i>)</b></dt><dd><p>
661 The sqlite_offset(X) function returns the byte offset in the database
662 file for the beginning of the record from which value would be read.
663 If X is not a column in an ordinary table, then sqlite_offset(X) returns
664 NULL. The value returned by sqlite_offset(X) might reference either the
665 original table or an index, depending on the query. If the value X would
666 normally be extracted from an index, the sqlite_offset(X) returns the
667 offset to the corresponding index record. If the value X would be
668 extracted from the original table, then sqlite_offset(X) returns the offset
669 to the table record.
670
671 <p>The sqlite_offset(X) SQL function is only available if SQLite is built
672 using the <a href="compile.html#enable_offset_sql_func">-DSQLITE_ENABLE_OFFSET_SQL_FUNC</a> compile-time option.
673 </dd>
674 <a name="sqlite_source_id"></a>
675 <dt><p><b>sqlite_source_id()</b></dt><dd><p>
676 The sqlite_source_id() function returns a string that identifies the
677 specific version of the source code that was used to build the SQLite
678 library. The string returned by sqlite_source_id() is
679 the date and time that the source code was checked in followed by
680 the SHA3-256 hash for that check-in. This function is
681 an SQL wrapper around the <a href="c3ref/libversion.html">sqlite3_sourceid()</a> C interface.
682 </dd>
683 <a name="sqlite_version"></a>
684 <dt><p><b>sqlite_version()</b></dt><dd><p>
685 The sqlite_version() function returns the version string for the SQLite
686 library that is running. This function is an SQL
687 wrapper around the <a href="c3ref/libversion.html">sqlite3_libversion()</a> C-interface.
688 </dd>
689 <a name="substr"></a>
690 <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>
691 The substr(X,Y,Z) function returns a substring of input string X that begins
692 with the Y-th character and which is Z characters long.
693 If Z is omitted then substr(X,Y) returns all characters through the end
694 of the string X beginning with the Y-th.
695 The left-most character of X is number 1. If Y is negative
696 then the first character of the substring is found by counting from the
697 right rather than the left. If Z is negative then
698 the abs(Z) characters preceding the Y-th character are returned.
699 If X is a string then indices count UTF code points.
700 If X is a BLOB then the indices count bytes.
701 <p>
702 "substring()" is an alias for "substr()" beginning with SQLite version 3.34.
703 </dd>
704 <a name="total_changes"></a>
705 <dt><p><b>total_changes()</b></dt><dd><p>
706 The total_changes() function returns the number of row changes
707 caused by INSERT, UPDATE or DELETE
708 statements since the current database connection was opened.
709 This function is a wrapper around the <a href="c3ref/total_changes.html">sqlite3_total_changes64()</a>
710 C/C++ interface.
711 </dd>
712 <a name="trim"></a>
713 <dt><p><b>trim(<i>X</i>)<br />trim(<i>X</i>,<i>Y</i>)</b></dt><dd><p>
714 The trim(X,Y) function returns a string formed by removing any and all
715 characters that appear in Y from both ends of X.
716 If the Y argument is omitted, trim(X) removes spaces from both ends of X.
717 </dd>
718 <a name="typeof"></a>
719 <dt><p><b>typeof(<i>X</i>)</b></dt><dd><p>
720 The typeof(X) function returns a string that indicates the <a href="datatype3.html">datatype</a> of
721 the expression X: "null", "integer", "real", "text", or "blob".
722 </dd>
723 <a name="unhex"></a>
724 <dt><p><b>unhex(<i>X</i>)<br />unhex(<i>X</i>,<i>Y</i>)</b></dt><dd><p>
725 The unhex(X,Y) function returns a BLOB value which is the decoding of the
726 hexadecimal string X. If X contains any
727 characters that are not hexadecimal digits and which are not in Y,
728 then unhex(X,Y) returns NULL. If Y is omitted, it is
729 understood to be an empty string and hence X must be a pure hexadecimal string.
730 All hexadecimal digits in X must occur in pairs, with both digits of each
731 pair beginning immediately adjacent to one another, or else unhex(X,Y)
732 returns NULL.
733 If either parameter X or Y is NULL, then unhex(X,Y) returns NULL.
734 The X input may contain an arbitrary mix of upper and lower case hexadecimal
735 digits.
736 Hexadecimal digits in Y have no affect on the translation of X. Only
737 characters in Y that are not hexadecimal digits are ignored in X.
738 <p>
739 See also: <a href="lang_corefunc.html#hex">hex()</a>
740 </dd>
741 <a name="unicode"></a>
742 <dt><p><b>unicode(<i>X</i>)</b></dt><dd><p>
743 The unicode(X) function returns the numeric unicode code point corresponding to
744 the first character of the string X. If the argument to unicode(X) is not a string
745 then the result is unspecified.
746 </dd>
747 <a name="unistr"></a>
748 <dt><p><b>unistr(<i>X</i>)</b></dt><dd><p>
749 The unistr(X) function interprets backslash escapes in input string X and returns
750 a new string with those escapes converted into the actual Unicode codepoints that
751 they represent. This function, added in SQLite version 3.50.0 (2025-05-29), is
752 intended to work the same as in PostgreSQL, SQL Server, and Oracle.
753 The PostgreSQL documentation says:
754 "Unicode characters can be specified as \XXXX (4 hexadecimal digits), \+XXXXXX
755 (6 hexadecimal digits), \uXXXX (4 hexadecimal digits), or \UXXXXXXXX (8 hexadecimal
756 digits). To specify a backslash, write two backslashes. All other characters are
757 taken literally."
758 </dd>
759 <a name="unistr_quote"></a>
760 <dt><p><b>unistr_quote(<i>X</i>)</b></dt><dd><p>
761 The unistr_quote(X) function returns the text of an SQL literal or constant
762 expression that encodes the value of its argument X and is suitable
763 for inclusion into an SQL statement. In most cases, the output of
764 unistr_quote(X) is identical to <a href="lang_corefunc.html#quote">quote(X)</a>. However, if X is text that
765 contains control characters in the range U+0001 through U+001f, then those
766 characters and any "\" characters in X are escaped using JSON-style backslash
767 escapes and the entire output is enclosed within "<tt><a href="lang_corefunc.html#unistr">unistr</a>(</tt>..<tt>)</tt>".
768 This makes the resulting text safe for display on devices that interpret
769 <a href="https://en.wikipedia.org/wiki/ANSI_escape_code">ANSI escape codes</a>.
770 </dd>
771 <a name="unlikely"></a>
772 <dt><p><b>unlikely(<i>X</i>)</b></dt><dd><p>
773 The unlikely(X) function returns the argument X unchanged.
774 The unlikely(X) function is a no-op that the code generator
775 optimizes away so that it consumes no CPU cycles at
776 run-time (that is, during calls to <a href="c3ref/step.html">sqlite3_step()</a>).
777 The purpose of the unlikely(X) function is to provide a hint
778 to the query planner that the argument X is a boolean value
779 that is usually not true. The unlikely(X) function is equivalent
780 to <a href="lang_corefunc.html#likelihood">likelihood</a>(X, 0.0625).
781 </dd>
782 <a name="upper"></a>
783 <dt><p><b>upper(<i>X</i>)</b></dt><dd><p>
784 The upper(X) function returns a copy of input string X in which all
785 lower-case ASCII characters are converted to their upper-case equivalent.
786 </dd>
787 <a name="zeroblob"></a>
788 <dt><p><b>zeroblob(<i>N</i>)</b></dt><dd><p>
789 The zeroblob(N) function returns a BLOB consisting of N bytes of 0x00.
790 SQLite manages these zeroblobs very efficiently. Zeroblobs can be used to
791 reserve space for a BLOB that is later written using
792 <a href="c3ref/blob_open.html">incremental BLOB I/O</a>.
793 This SQL function is implemented using the <a href="c3ref/result_blob.html">sqlite3_result_zeroblob()</a>
794 routine from the C/C++ interface.
795 </dd>
796
797 </dl>
798 <p align="center"><small><i>This page was last updated on 2026-05-07 15:07:22Z </small></i></p>
799