<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html><head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<title>SQLite Query Language: Core Functions</title>
<style type="text/css">
body {
    margin: auto;
    font-family: Verdana, sans-serif;
    padding: 8px 1%;
}

a { color: #044a64 }
a:visited { color: #734559 }

.logo { position:absolute; margin:3px; }
.tagline {
  float:right;
  text-align:right;
  font-style:italic;
  width:300px;
  margin:12px;
  margin-top:58px;
}

.menubar {
  clear: both;
  border-radius: 8px;
  background: #044a64;
  padding: 0px;
  margin: 0px;
  cell-spacing: 0px;
}    
.toolbar {
  text-align: center;
  line-height: 1.6em;
  margin: 0;
  padding: 0px 8px;
}
.toolbar a { color: white; text-decoration: none; padding: 6px 12px; }
.toolbar a:visited { color: white; }
.toolbar a:hover { color: #044a64; background: white; }

.content    { margin: 5%; }
.content dt { font-weight:bold; }
.content dd { margin-bottom: 25px; margin-left:20%; }
.content ul { padding:0px; padding-left: 15px; margin:0px; }

/* Things for "fancyformat" documents start here. */
.fancy img+p {font-style:italic}
.fancy .codeblock i { color: darkblue; }
.fancy h1,.fancy h2,.fancy h3,.fancy h4 {font-weight:normal;color:#044a64}
.fancy h2 { margin-left: 10px }
.fancy h3 { margin-left: 20px }
.fancy h4 { margin-left: 30px }
.fancy th {white-space:nowrap;text-align:left;border-bottom:solid 1px #444}
.fancy th, .fancy td {padding: 0.2em 1ex; vertical-align:top}
.fancy #toc a        { color: darkblue ; text-decoration: none }
.fancy .todo         { color: #AA3333 ; font-style : italic }
.fancy .todo:before  { content: 'TODO:' }
.fancy p.todo        { border: solid #AA3333 1px; padding: 1ex }
.fancy img { display:block; }
.fancy :link:hover, .fancy :visited:hover { background: wheat }
.fancy p,.fancy ul,.fancy ol { margin: 1em 5ex }
.fancy li p { margin: 1em 0 }
/* End of "fancyformat" specific rules. */

</style>
  
</head>
<body>
<div><!-- container div to satisfy validator -->

<a href="index.html">
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite Logo"
 border="0"></a>
<div><!-- IE hack to prevent disappearing logo--></div>
<div class="tagline">Small. Fast. Reliable.<br>Choose any three.</div>

<table width=100% class="menubar"><tr>
  <td width=100%>
  <div class="toolbar">
    <a href="about.html">About</a>
    <a href="sitemap.html">Sitemap</a>
    <a href="docs.html">Documentation</a>
    <a href="download.html">Download</a>
    <a href="copyright.html">License</a>
    <a href="news.html">News</a>
    <a href="support.html">Support</a>
  </div>
<script>
  gMsg = "Search SQLite Docs..."
  function entersearch() {
    var q = document.getElementById("q");
    if( q.value == gMsg ) { q.value = "" }
    q.style.color = "black"
    q.style.fontStyle = "normal"
  }
  function leavesearch() {
    var q = document.getElementById("q");
    if( q.value == "" ) { 
      q.value = gMsg
      q.style.color = "#044a64"
      q.style.fontStyle = "italic"
    }
  }
  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;
  }
</script>
<td>
    <div style="padding:0 1em 0px 0;white-space:nowrap">
    <form name=f method="GET" action="http://www.sqlite.org/search">
      <input id=q name=q type=text
       onfocus="entersearch()" onblur="leavesearch()" style="width:24ex;padding:1px 1ex; border:solid white 1px; font-size:0.9em ; font-style:italic;color:#044a64;" value="Search SQLite Docs...">
      <input type=submit value="Go" style="border:solid white 1px;background-color:#044a64;color:white;font-size:0.9em;padding:0 1ex">
    </form>
    </div>
  </table>

<div class=startsearch></div>
  
<h1 align="center">SQL As Understood By SQLite</h1><p><a href="lang.html">[Top]</a></p><h2>Core Functions</h2>

<p>The core functions shown below are available by default. 
<a href="lang_datefunc.html">Date &amp; Time functions</a> and
<a href="lang_aggfunc.html">aggregate 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>

<table border=0 cellpadding=10>
<tr><td valign="top" align="right" width="120"><a name="abs"></a>
abs(<i>X</i>)</td><td valign="top">
  The abs(X) function returns the absolute value of the numeric
  argument X.  Abs(X) returns NULL if X is NULL. 
  Abs(X) return 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.
</td></tr><tr><td valign="top" align="right" width="120"><a name="changes"></a>
changes()</td><td valign="top">
  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_changes()</a>
  C/C++ function and hence follows the same rules for counting changes.
</td></tr><tr><td valign="top" align="right" width="120"><a name="char"></a>
char(<i>X1</i>,<i>X2</i>,...,<i>XN</i>)</td><td valign="top">
  The char(X1,X2,...,XN) function returns a string composed of characters having the
   unicode code point values of integers X1 through XN, respectively.
</td></tr><tr><td valign="top" align="right" width="120"><a name="coalesce"></a>
coalesce(<i>X</i>,<i>Y</i>,...)</td><td valign="top">
  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.
</td></tr><tr><td valign="top" align="right" width="120"><a name="glob"></a>
glob(<i>X</i>,<i>Y</i>)</td><td valign="top">
  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.
  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.
</td></tr><tr><td valign="top" align="right" width="120"><a name="ifnull"></a>
ifnull(<i>X</i>,<i>Y</i>)</td><td valign="top">
  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.
</td></tr><tr><td valign="top" align="right" width="120"><a name="instr"></a>
instr(<i>X</i>,<i>Y</i>)</td><td valign="top">
  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.
</td></tr><tr><td valign="top" align="right" width="120"><a name="hex"></a>
hex(<i>X</i>)</td><td valign="top">
  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.
</td></tr><tr><td valign="top" align="right" width="120"><a name="last_insert_rowid"></a>
last_insert_rowid()</td><td valign="top">
  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.
</td></tr><tr><td valign="top" align="right" width="120"><a name="length"></a>
length(<i>X</i>)</td><td valign="top">
  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.
</td></tr><tr><td valign="top" align="right" width="120"><a name="like"></a>
like(<i>X</i>,<i>Y</i>)<br />like(<i>X</i>,<i>Y</i>,<i>Z</i>)</td><td valign="top">
  The like() function is used to implement the
  "<b>Y LIKE X &#91;ESCAPE Z&#93;</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.
  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.
</td></tr><tr><td valign="top" align="right" width="120"><a name="likelihood"></a>
likelihood(<i>X</i>,<i>Y</i>)</td><td valign="top">
  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).
</td></tr><tr><td valign="top" align="right" width="120"><a name="load_extension"></a>
load_extension(<i>X</i>)<br />load_extension(<i>X</i>,<i>Y</i>)</td><td valign="top">
  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 loaded is turned off by default and must
  be enabled by a prior call to <a href="c3ref/enable_load_extension.html">sqlite3_enable_load_extension()</a>.</p>
</td></tr><tr><td valign="top" align="right" width="120"><a name="lower"></a>
lower(<i>X</i>)</td><td valign="top">
  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.
</td></tr><tr><td valign="top" align="right" width="120"><a name="ltrim"></a>
ltrim(<i>X</i>)<br />ltrim(<i>X</i>,<i>Y</i>)</td><td valign="top">
  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.
</td></tr><tr><td valign="top" align="right" width="120"><a name="maxoreunc"></a>
max(<i>X</i>,<i>Y</i>,...)</td><td valign="top">
  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#minggunc">aggregate function</a> if given only a single argument.
</td></tr><tr><td valign="top" align="right" width="120"><a name="minoreunc"></a>
min(<i>X</i>,<i>Y</i>,...)</td><td valign="top">
  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#maxggunc">aggregate function</a> if given
  only a single argument.
</td></tr><tr><td valign="top" align="right" width="120"><a name="nullif"></a>
nullif(<i>X</i>,<i>Y</i>)</td><td valign="top">
  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 is used.
</td></tr><tr><td valign="top" align="right" width="120"><a name="printf"></a>
printf(<i>FORMAT</i>,...)</td><td valign="top">
  The printf(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.
</td></tr><tr><td valign="top" align="right" width="120"><a name="quote"></a>
quote(<i>X</i>)</td><td valign="top">
  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.
</td></tr><tr><td valign="top" align="right" width="120"><a name="random"></a>
random()</td><td valign="top">
  The random() function returns a pseudo-random integer
  between -9223372036854775808 and +9223372036854775807.
</td></tr><tr><td valign="top" align="right" width="120"><a name="randomblob"></a>
randomblob(<i>N</i>)</td><td valign="top">
  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>
</td></tr><tr><td valign="top" align="right" width="120"><a name="replace"></a>
replace(<i>X</i>,<i>Y</i>,<i>Z</i>)</td><td valign="top">
  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.
</td></tr><tr><td valign="top" align="right" width="120"><a name="round"></a>
round(<i>X</i>)<br />round(<i>X</i>,<i>Y</i>)</td><td valign="top">
  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, it is assumed to be 0.
</td></tr><tr><td valign="top" align="right" width="120"><a name="rtrim"></a>
rtrim(<i>X</i>)<br />rtrim(<i>X</i>,<i>Y</i>)</td><td valign="top">
  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.
</td></tr><tr><td valign="top" align="right" width="120"><a name="soundex"></a>
soundex(<i>X</i>)</td><td valign="top">
  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.
</td></tr><tr><td valign="top" align="right" width="120"><a name="sqlite_compileoption_get"></a>
sqlite_compileoption_get(<i>N</i>)</td><td valign="top">
  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>.
</td></tr><tr><td valign="top" align="right" width="120"><a name="sqlite_compileoption_used"></a>
sqlite_compileoption_used(<i>X</i>)</td><td valign="top">
  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.
</td></tr><tr><td valign="top" align="right" width="120"><a name="sqlite_source_id"></a>
sqlite_source_id()</td><td valign="top">
  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() begins with
  the date and time that the source code was checked in and is follows by
  an SHA1 hash that uniquely identifies the source tree.  This function is
  an SQL wrapper around the <a href="c3ref/libversion.html">sqlite3_sourceid()</a> C interface.
</td></tr><tr><td valign="top" align="right" width="120"><a name="sqlite_version"></a>
sqlite_version()</td><td valign="top">
  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.
</td></tr><tr><td valign="top" align="right" width="120"><a name="substr"></a>
substr(<i>X</i>,<i>Y</i>,<i>Z</i>)<br />substr(<i>X</i>,<i>Y</i>)</td><td valign="top">
  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.
</td></tr><tr><td valign="top" align="right" width="120"><a name="total_changes"></a>
total_changes()</td><td valign="top">
  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_changes()</a>
  C/C++ interface.
</td></tr><tr><td valign="top" align="right" width="120"><a name="trim"></a>
trim(<i>X</i>)<br />trim(<i>X</i>,<i>Y</i>)</td><td valign="top">
  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.
</td></tr><tr><td valign="top" align="right" width="120"><a name="typeof"></a>
typeof(<i>X</i>)</td><td valign="top">
  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".
</td></tr><tr><td valign="top" align="right" width="120"><a name="unlikely"></a>
unlikely(<i>X</i>)</td><td valign="top">
  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).
</td></tr><tr><td valign="top" align="right" width="120"><a name="unicode"></a>
unicode(<i>X</i>)</td><td valign="top">
  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.
</td></tr><tr><td valign="top" align="right" width="120"><a name="upper"></a>
upper(<i>X</i>)</td><td valign="top">
  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.
</td></tr><tr><td valign="top" align="right" width="120"><a name="zeroblob"></a>
zeroblob(<i>N</i>)</td><td valign="top">
  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.
</td></tr>
</table>


