diff options
Diffstat (limited to 'www/lang_corefunc.html')
| -rw-r--r-- | www/lang_corefunc.html | 737 |
1 files changed, 737 insertions, 0 deletions
diff --git a/www/lang_corefunc.html b/www/lang_corefunc.html new file mode 100644 index 0000000..95f8927 --- /dev/null +++ b/www/lang_corefunc.html @@ -0,0 +1,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> + |