| 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 & 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 [ESCAPE Z]</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 |
|