A literal value is an integer number or a floating point number.Scientific notation is supported.  The "." character is always usedas the decimal point even if the locale setting specifies "," forthis role - the use of "," for the decimal point would result insyntactic ambiguity.  A string constant is formed by enclosing thestring in single quotes (').  A single quote within the string canbe encoded by putting two single quotes in a row - as in Pascal.C-style escapes using the backslash character are not supported becausethey are not standard SQL.BLOB literals are string literals containing hexadecimal data andpreceded by a single "x" or "X" character.  For example:</p><blockquote><pre>X'53514697465'</pre></blockquote><p>A literal value can also be the token "NULL".</p><p>A parameter specifies a placeholder in the expression for a literalvalue that is filled in at runtime using the<a href="capi3ref.html#sqlite3_bind_int">sqlite3_bind</a> API.Parameters can take several forms:</p<blockquote><table><tr><td align="right" valign="top"><b>?</b><i>NNN</i></td><td width="20"></td><td>A question mark followed by a number <i>NNN</i> holds a spot for theNNN-th parameter.  NNN must be between 1 and 999.</td></tr><tr><td align="right" valign="top"><b>?</b></td><td width="20"></td><td>A question mark that is not followed by a number holds a spot forthe next unused parameter.</td></tr><tr><td align="right" valign="top"><b>:</b><i>AAAA</i></td><td width="20"></td><td>A colon followed by an identifier name holds a spot for a namedparameter with the name AAAA.  Named parameters are also numbered.The number assigned is the next unused number.  To avoid confusion,it is best to avoid mixing named and numbered parameters.</td></tr><tr><td align="right" valign="top"><b>$</b><i>AAAA</i></td><td width="20"></td><td>A dollar-sign followed by an identifier name also holds a spot for a namedparameter with the name AAAA.  The identifier name in this case can includeone or more occurances of "::" and a suffix enclosed in "(...)" containingany text at all.  This syntax is the form of a variable name in the Tclprogramming language.</td></tr></table></blockquote><p>Parameters that are not assigned values using<a href="capi3ref.html#sqlite3_bind_int">sqlite3_bind</a> are treatedas NULL.</p><a name="like"></a><p>The LIKE operator does a pattern matching comparison. The operandto the right contains the pattern, the left hand operand contains thestring to match against the pattern. }puts "A percent symbol [Operator %] in the pattern matches anysequence of zero or more characters in the string.  An underscore[Operator _] in the pattern matches any single character in thestring.  Any other character matches itself or it's lower/upper caseequivalent (i.e. case-insensitive matching).  (A bug: SQLite onlyunderstands upper/lower case for 7-bit Latin characters.  Hence theLIKE operator is case sensitive for 8-bit iso8859 characters or UTF-8characters.  For example, the expression <b>'a'&nbsp;LIKE&nbsp;'A'</b>is TRUE but <b>'&aelig;'&nbsp;LIKE&nbsp;'&AElig;'</b> is FALSE.).</p>"puts {<p>If the optional ESCAPE clause is present, then the expressionfollowing the ESCAPE keyword must evaluate to a string consisting ofa single character. This character may be used in the LIKE patternto include literal percent or underscore characters. The escapecharacter followed by a percent symbol, underscore or itself matches aliteral percent symbol, underscore or escape character in the string,respectively. The infix LIKE operator is implemented by calling theuser function <a href="#likeFunc"> like(<i>X</i>,<i>Y</i>)</a>.</p>}puts {The LIKE operator is not case sensitive and will match upper casecharacters on one side against lower case characters on the other.  (A bug: SQLite only understands upper/lower case for 7-bit Latincharacters.  Hence the LIKE operator is case sensitive for 8-bitiso8859 characters or UTF-8 characters.  For example, the expression<b>'a'&nbsp;LIKE&nbsp;'A'</b> is TRUE but<b>'&aelig;'&nbsp;LIKE&nbsp;'&AElig;'</b> is FALSE.).</p><p>The infix LIKEoperator is implemented by calling the user function <a href="#likeFunc">like(<i>X</i>,<i>Y</i>)</a>.  If an ESCAPE clause is present, it addsa third parameter to the function call. If the functionality of LIKE can beoverridden by defining an alternative implementation of thelike() SQL function.</p></p><a name="glob"></a><p>The GLOB operator is similar to LIKE but uses the Unixfile globbing syntax for its wildcards.  Also, GLOB is casesensitive, unlike LIKE.  Both GLOB and LIKE may be preceded bythe NOT keyword to invert the sense of the test.  The infix GLOB operator is implemented by calling the user function <a href="#globFunc">glob(<i>X</i>,<i>Y</i>)</a> and can be modified by overridingthat function.</p><a name="regexp"></a><p>The REGEXP operator is a special syntax for the regexp()user function.  No regexp() user function is defined by defaultand so use of the REGEXP operator will normally result in anerror message.  If a user-defined function named "regexp"is defined at run-time, that function will be called in orderto implement the REGEXP operator.</p><p>A column name can be any of the names defined in the CREATE TABLEstatement or one of the following special identifiers: "<b>ROWID</b>","<b>OID</b>", or "<b>_ROWID_</b>".These special identifiers all describe theunique random integer key (the "row key") associated with every row of every table.The special identifiers only refer to the row key if the CREATE TABLEstatement does not define a real column with the same name.  Row keysact like read-only columns.  A row key can be used anywhere a regularcolumn can be used, except that you cannot change the valueof a row key in an UPDATE or INSERT statement."SELECT * ..." does not return the row key.</p><p>SELECT statements can appear in expressions as either theright-hand operand of the IN operator, as a scalar quantity, oras the operand of an EXISTS operator.As a scalar quantity or the operand of an IN operator,the SELECT should have only a single column in itsresult.  Compound SELECTs (connected with keywords like UNION orEXCEPT) are allowed.With the EXISTS operator, the columns in the result set of the SELECT areignored and the expression returns TRUE if one or more rows existand FALSE if the result set is empty.If no terms in the SELECT expression refer to value in the containingquery, then the expression is evaluated once prior to any otherprocessing and the result is reused as necessary.  If the SELECT expressiondoes contain variables from the outer query, then the SELECT is reevaluatedevery time it is needed.</p><p>When a SELECT is the right operand of the IN operator, the INoperator returns TRUE if the result of the left operand is any ofthe values generated by the select.  The IN operator may be precededby the NOT keyword to invert the sense of the test.</p><p>When a SELECT appears within an expression but is not the rightoperand of an IN operator, then the first row of the result of theSELECT becomes the value used in the expression.  If the SELECT yieldsmore than one result row, all rows after the first are ignored.  Ifthe SELECT yields no rows, then the value of the SELECT is NULL.</p><p>A CAST expression changes the datatype of the <expr> into thetype specified by &lt;type&gt;. &lt;type&gt; can be any non-empty type name that is validfor the type in a column definition of a CREATE TABLE statement.</p><p>Both simple and aggregate functions are supported.  A simplefunction can be used in any expression.  Simple functions returna result immediately based on their inputs.  Aggregate functionsmay only be used in a SELECT statement.  Aggregate functions computetheir result across all rows of the result set.</p><p>The functions shown below are available by default.  Additionalfunctions may be written in C and added to the database engine usingthe <a href="capi3ref.html#cfunc">sqlite3_create_function()</a>API.</p><table border=0 cellpadding=10><tr><td valign="top" align="right" width=120>abs(<i>X</i>)</td><td valign="top">Return the absolute value of argument <i>X</i>.</td></tr><tr><td valign="top" align="right">coalesce(<i>X</i>,<i>Y</i>,...)</td><td valign="top">Return a copy of the first non-NULL argument.  Ifall arguments are NULL then NULL is returned.  There must be at least 2 arguments.</td></tr><tr><a name="globFunc"></a><td valign="top" align="right">glob(<i>X</i>,<i>Y</i>)</td><td valign="top">This function is used to implement the"<b>X GLOB Y</b>" syntax of SQLite.  The<a href="capi3ref.html#sqlite3_create_function">sqlite3_create_function()</a> interface canbe used to override this function and thereby change the operationof the <a href="#globFunc">GLOB</a> operator.</td></tr><tr><td valign="top" align="right">ifnull(<i>X</i>,<i>Y</i>)</td><td valign="top">Return a copy of the first non-NULL argument.  Ifboth arguments are NULL then NULL is returned. This behaves the same as <b>coalesce()</b> above.</td></tr><tr><td valign="top" align="right">last_insert_rowid()</td><td valign="top">Return the ROWID of the last row insert from thisconnection to the database.  This is the same value that would be returnedfrom the <b>sqlite_last_insert_rowid()</b> API function.</td></tr><tr><td valign="top" align="right">length(<i>X</i>)</td><td valign="top">Return the string length of <i>X</i> in characters.If SQLite is configured to support UTF-8, then the number of UTF-8characters is returned, not the number of bytes.</td></tr><tr><a name="likeFunc"></a><td valign="top" align="right">like(<i>X</i>,<i>Y</i> [,<i>Z</i>])</td><td valign="top">This function is used to implement the "<b>X LIKE Y [ESCAPE Z]</b>"syntax of SQL. If the optional ESCAPE clause is present, then theuser-function is invoked with three arguments. Otherwise, it isinvoked with two arguments only. The <a href="capi3ref.html#sqlite3_create_function">sqlite_create_function()</a> interface can be used to override thisfunction and thereby change the operation of the <ahref= "#like">LIKE</a> operator. When doing this, it may be importantto override both the two and three argument versions of the like() function. Otherwise, different code may be called to implement theLIKE operator depending on whether or not an ESCAPE clause was specified.</td></tr><tr><td valign="top" align="right">lower(<i>X</i>)</td><td valign="top">Return a copy of string <i>X</i> will all charactersconverted to lower case.  The C library <b>tolower()</b> routine is usedfor the conversion, which means that this function might notwork correctly on UTF-8 characters.</td></tr><tr><td valign="top" align="right">max(<i>X</i>,<i>Y</i>,...)</td><td valign="top">Return the argument with the maximum value.  Argumentsmay be strings in addition to numbers.  The maximum value is determinedby the usual sort order.  Note that <b>max()</b> is a simple function whenit has 2 or more arguments but converts to an aggregate function if givenonly a single argument.</td></tr><tr><td valign="top" align="right">min(<i>X</i>,<i>Y</i>,...)</td><td valign="top">Return the argument with the minimum value.  Argumentsmay be strings in addition to numbers.  The minimum value is determinedby the usual sort order.  Note that <b>min()</b> is a simple function whenit has 2 or more arguments but converts to an aggregate function if givenonly a single argument.</td></tr><tr><td valign="top" align="right">nullif(<i>X</i>,<i>Y</i>)</td><td valign="top">Return the first argument if the arguments are different, otherwise return NULL.</td></tr><tr><td valign="top" align="right">quote(<i>X</i>)</td><td valign="top">This routine returns a string which is the value ofits argument suitable for inclusion into another SQL statement.Strings are surrounded by single-quotes with escapes on interior quotesas needed.  BLOBs are encoded as hexadecimal literals.The current implementation of VACUUM uses this function.  The functionis also useful when writing triggers to implement undo/redo functionality.</td></tr><tr><td valign="top" align="right">random(*)</td><td valign="top">Return a random integer between -2147483648 and+2147483647.</td></tr><tr><td valign="top" align="right">round(<i>X</i>)<br>round(<i>X</i>,<i>Y</i>)</td><td valign="top">Round off the number <i>X</i> to <i>Y</i> digits to theright of the decimal point.  If the <i>Y</i> argument is omitted, 0 is assumed.</td></tr><tr><td valign="top" align="right">soundex(<i>X</i>)</td><td valign="top">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 optionis used when SQLite is built.</td></tr><tr><td valign="top" align="right">sqlite_version(*)</td><td valign="top">Return the version string for the SQLite librarythat is running.  Example:  "2.8.0"</td></tr><tr><td valign="top" align="right">substr(<i>X</i>,<i>Y</i>,<i>Z</i>)</td><td valign="top">Return a substring of input string <i>X</i> that beginswith the <i>Y</i>-th character and which is <i>Z</i> characters long.The left-most character of <i>X</i> is number 1.  If <i>Y</i> is negativethe the first character of the substring is found by counting from theright rather than the left.  If SQLite is configured to support UTF-8,then characters indices refer to actual UTF-8 characters, not bytes.</td></tr><tr><td valign="top" align="right">typeof(<i>X</i>)</td><td valign="top">Return the type of the expression <i>X</i>.  The only return values are "null", "integer", "real", "text", and "blob".SQLite's type handling is explained in <a href="datatype3.html">Datatypes in SQLite Version 3</a>.</td></tr><tr><td valign="top" align="right">upper(<i>X</i>)</td><td valign="top">Return a copy of input string <i>X</i> converted to allupper-case letters.  The implementation of this function uses the C libraryroutine <b>toupper()</b> which means it may not work correctly on UTF-8 strings.</td></tr></table>