BlitzNG
/
database.mod
mirror da https://github.com/bmx-ng/database.mod.git


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

a { color: #45735f }
a:visited { color: #734559 }

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

.toolbar {
  font-variant: small-caps;
  text-align: center;
  line-height: 1.6em;
  margin: 0;
  padding:1px 8px;
}
.toolbar a { color: white; text-decoration: none; padding: 6px 12px; }
.toolbar a:visited { color: white; }
.toolbar a:hover { color: #80a796; 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; }

/* rounded corners */
.se  { background: url(images/se.png) 100% 100% no-repeat #80a796}
.sw  { background: url(images/sw.png) 0% 100% no-repeat }
.ne  { background: url(images/ne.png) 100% 0% no-repeat }
.nw  { background: url(images/nw.png) 0% 0% no-repeat }

</style>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
  
</head>
<body>
<div><!-- container div to satisfy validator -->

<a href="lang.html">
           <h2 align="center">SQL As Understood By SQLite</h2></a><h1>Core Functions</h1>

<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. </p>

<table border=0 cellpadding=10>
<tr><td valign="top" align="right" width="120">abs(<i>X</i>)</td><td valign="top"><a name="abs"></a>

  Return the absolute value of the numeric
  argument <i>X</i>.  Return NULL if <i>X</i> is NULL.  Return 0.0 if
  <i>X</i> is not a numeric value.
</td></tr><tr><td valign="top" align="right" width="120">changes()</td><td valign="top"><a name="changes"></a>

  Return the number of database rows that were changed or inserted or
  deleted by the most recently complete SQL.
</td></tr><tr><td valign="top" align="right" width="120">coalesce(<i>X</i>,<i>Y</i>,...)</td><td valign="top"><a name="coalesce"></a>

  Return a copy of the first non-NULL argument.  If
  all arguments are NULL then NULL is returned.  There must be at least 
  2 arguments.
</td></tr><tr><td valign="top" align="right" width="120">glob(<i>X</i>,<i>Y</i>)</td><td valign="top"><a name="glob"></a>

  This function is used to implement the
  "<b>Y GLOB X</b>" syntax of SQLite.
  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.
  </td>
</td></tr><tr><td valign="top" align="right" width="120">ifnull(<i>X</i>,<i>Y</i>)</td><td valign="top"><a name="ifnull"></a>

  Return a copy of the first non-NULL argument.  If
  both arguments are NULL then NULL is returned. The ifnull() functions and
  <a href="lang_corefunc.html#coalesce">coalesce()</a> with two arguments are interchangeable.</td>
</td></tr><tr><td valign="top" align="right" width="120">hex(<i>X</i>)</td><td valign="top"><a name="hex"></a>

  The argument is interpreted as a BLOB.  The result
  is a hexadecimal rendering of the content of that blob.  
</td></tr><tr><td valign="top" align="right" width="120">last_insert_rowid()</td><td valign="top"><a name="last_insert_rowid"></a>

  Return the <a href="lang_createtable.html#rowid">ROWID</a>
  of the last row insert from this
  connection to the database. 
  This is the same value that would be returned
  from the lastInsertedId() method.
</td></tr><tr><td valign="top" align="right" width="120">length(<i>X</i>)</td><td valign="top"><a name="length"></a>

  Return the string length of <i>X</i> in characters if <i>X</i> is a string,
  or in bytes if <i>X</i> is a blob.
</td></tr><tr><td valign="top" align="right" width="120">like(<i>X</i>,<i>Y</i>)<br></br>like(<i>X</i>,<i>Y</i>,<i>Z</i>)</td><td valign="top"><a name="like"></a>

  This function is used to implement the "<b>Y LIKE X &#91;ESCAPE Z&#93;</b>"
  syntax of SQL. If the optional ESCAPE clause is present, then the
  user-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 sqlite3_create_function() interface can be used to override this
  function and thereby change the operation of the
  <a href="lang_expr.html#like">LIKE</a> operator. When doing this, 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">load_extension(<i>X</i>)<br></br>load_extension(<i>X</i>,<i>Y</i>)</td><td valign="top"><a name="load_extension"></a>

  Load SQLite extensions out of the shared library
  file named <i>X</i> using the entry point <i>Y</i>.  The result
  is a NULL.  If <i>Y</i> is omitted then the default entry point
  of <b>sqlite3_extension_init</b> is used.  This function raises
  an exception if the extension fails to load or initialize correctly.

  <p>This function will fail if the extension attempts to modify
  or delete a 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
  sqlite3_load_extension() C-language API.</p>
</td></tr><tr><td valign="top" align="right" width="120">lower(<i>X</i>)</td><td valign="top"><a name="lower"></a>

  Return a copy of string <i>X</i> 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">ltrim(<i>X</i>)<br></br>ltrim(<i>X</i>,<i>Y</i>)</td><td valign="top"><a name="ltrim"></a>

  Return a string formed by removing any and all
  characters that appear in <i>Y</i> from the left side of <i>X</i>.
  If the <i>Y</i> argument is omitted, spaces are removed.
</td></tr><tr><td valign="top" align="right" width="120">max(<i>X</i>,<i>Y</i>,...)</td><td valign="top"><a name="maxCoreFunc"></a>

  Return the argument with the maximum value.  Or return NULL if any argument
  is NULL.
  Note that <b>max()</b> is a simple function when
  it has 2 or more arguments but converts to an aggregate function if given
  only a single argument.
</td></tr><tr><td valign="top" align="right" width="120">min(<i>X</i>,<i>Y</i>,...)</td><td valign="top"><a name="minCoreFunc"></a>

  Return the argument with the minimum value. 
  Note that <b>min()</b> is a simple function when
  it has 2 or more arguments but converts to an aggregate function if given
  only a single argument.
</td></tr><tr><td valign="top" align="right" width="120">nullif(<i>X</i>,<i>Y</i>)</td><td valign="top"><a name="nullif"></a>

  Return the first argument if the arguments are different, 
  otherwise return NULL.
</td></tr><tr><td valign="top" align="right" width="120">quote(<i>X</i>)</td><td valign="top"><a name="quote"></a>

  This routine returns a string which is the value of
  its argument suitable for inclusion into another SQL statement.
  Strings are surrounded by single-quotes with escapes on interior quotes
  as needed.  BLOBs are encoded as hexadecimal literals.
  SQLite uses this function internally in its implementation of <a href="lang_vacuum.html">VACUUM</a>
  so if this function is overloading to provide incompatible behavior, the
  <a href="lang_vacuum.html">VACUUM</a> command will likely cease to work.
</td></tr><tr><td valign="top" align="right" width="120">random()</td><td valign="top"><a name="random"></a>

  Return a pseudo-random integer
  between -9223372036854775808 and +9223372036854775807.
</td></tr><tr><td valign="top" align="right" width="120">randomblob(<i>N</i>)</td><td valign="top"><a name="randomblob"></a>

  Return an <i>N</i>-byte blob containing pseudo-random bytes.
  <i>N</i> should be a postive integer.

  <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">replace(<i>X</i>,<i>Y</i>,<i>Z</i>)</td><td valign="top"><a name="replace"></a>

  Return a string formed by substituting string <i>Z</i> for
  every occurrance of string <i>Y</i> in string <i>X</i>.  The <a href="datatype3.html#collation">BINARY</a>
  collating sequence is used for comparisons.  If <i>Y</i> is an empty
  string then return <i>X</i> unchanged.
</td></tr><tr><td valign="top" align="right" width="120">round(<i>X</i>)<br></br>round(<i>X</i>,<i>Y</i>)</td><td valign="top"><a name="round"></a>

  Round off the number <i>X</i> to <i>Y</i> digits to the
  right of the decimal point.  If the <i>Y</i> argument is omitted, 0 is 
  assumed.
</td></tr><tr><td valign="top" align="right" width="120">rtrim(<i>X</i>)<br></br>rtrim(<i>X</i>,<i>Y</i>)</td><td valign="top"><a name="rtrim"></a>

  Return a string formed by removing any and all
  characters that appear in <i>Y</i> from the right side of <i>X</i>.
  If the <i>Y</i> argument is omitted, spaces are removed.
</td></tr><tr><td valign="top" align="right" width="120">soundex(<i>X</i>)</td><td valign="top"><a name="soundex"></a>

  Compute the soundex encoding of the string <i>X</i>.
  The string "?000" is returned if the argument is NULL.
  This function is omitted from SQLite by default.
  It is only available the -DSQLITE_SOUNDEX=1 compiler option
  is used when SQLite is built.
</td></tr><tr><td valign="top" align="right" width="120">sqlite_source_id()</td><td valign="top"><a name="sqlite_source_id"></a>

  Return a string that identifies the specific version of the source code
  that was used to build the SQLite library.  The return string 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. 
</td></tr><tr><td valign="top" align="right" width="120">sqlite_version()</td><td valign="top"><a name="sqlite_version"></a>

  Return the version string for the SQLite library
  that is running.  Example:  "3.5.9".
</td></tr><tr><td valign="top" align="right" width="120">substr(<i>X</i>,<i>Y</i>,<i>Z</i>)<br></br>substr(<i>X</i>,<i>Y</i>)</td><td valign="top"><a name="substr"></a>

  Return a substring of input string <i>X</i> that begins
  with the <i>Y</i>-th character and which is <i>Z</i> characters long.
  If <i>Z</i> is omitted then all character through the end of the string
  are returned.
  The left-most character of <i>X</i> is number 1.  If <i>Y</i> is negative
  the the first character of the substring is found by counting from the
  right rather than the left.  If <i>X</i> is string
  then characters indices refer to actual UTF-8 characters.  If
  <i>X</i> is a BLOB then the indices refer to bytes.
</td></tr><tr><td valign="top" align="right" width="120">total_changes()</td><td valign="top"><a name="total_changes"></a>

  Return the number of row changes caused by INSERT, UPDATE or DELETE
  statements since the current database connection was opened.
  
</td></tr><tr><td valign="top" align="right" width="120">trim(<i>X</i>)<br></br>trim(<i>X</i>,<i>Y</i>)</td><td valign="top"><a name="trim"></a>

  Return a string formed by removing any and all
  characters that appear in <i>Y</i> from both ends of <i>X</i>.
  If the <i>Y</i> argument is omitted, spaces are removed.
</td></tr><tr><td valign="top" align="right" width="120">typeof(<i>X</i>)</td><td valign="top"><a name="typeof"></a>

  Return the <a href="datatype3.html">datatype</a> of the expression <i>X</i>.  The only 
  return values are "null", "integer", "real", "text", and "blob".
</td></tr><tr><td valign="top" align="right" width="120">upper(<i>X</i>)</td><td valign="top"><a name="upper"></a>

  Return a copy of input string <i>X</i> converted to all
  upper-case letters.  The implementation of this function uses the C library
  routine <b>toupper()</b> which means it may not work correctly on 
  non-ASCII UTF-8 strings.
</td></tr><tr><td valign="top" align="right" width="120">zeroblob(<i>N</i>)</td><td valign="top"><a name="zeroblob"></a>

  Return 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 
  incremental BLOB I/O.
  
</td></tr>
</table>