| <html> |
| <head> |
| <META http-equiv="Content-Type" content="text/html; charset=UTF-8"> |
| <title>Chapter 10. Built In Functions</title> |
| <link href="../docbook.css" rel="stylesheet" type="text/css"> |
| <meta content="DocBook XSL-NS Stylesheets V1.74.0" name="generator"> |
| <meta name="keywords" content="Hsqldb, HyperSQL, SQL"> |
| <meta name="keywords" content="Hsqldb, HyperSQL, Database, JDBC, Java"> |
| <link rel="home" href="index.html" title="HyperSQL User Guide"> |
| <link rel="up" href="index.html" title="HyperSQL User Guide"> |
| <link rel="prev" href="triggers-chapt.html" title="Chapter 9. Triggers"> |
| <link rel="next" href="deployment-chapt.html" title="Chapter 11. System Management and Deployment Issues"> |
| </head> |
| <body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"> |
| <div class="navheader"> |
| <table summary="Navigation header" width="100%"> |
| <tr> |
| <td align="left" width="30%"><a accesskey="p" href="triggers-chapt.html"><img src="../images/db/prev.png" alt="Prev"></a> </td><td align="center" width="40%" style="font-weight:bold;">Chapter 10. Built In Functions</td><td align="right" width="30%"> <a accesskey="n" href="deployment-chapt.html"><img src="../images/db/next.png" alt="Next"></a></td> |
| </tr> |
| <tr> |
| <td valign="top" align="left" width="30%">Chapter 9. Triggers </td><td align="center" width="40%"><a accesskey="h" href="index.html"><img src="../images/db/home.png" alt="Home"></a></td><td valign="top" align="right" width="30%"> Chapter 11. System Management and Deployment |
| Issues</td> |
| </tr> |
| </table> |
| </div> |
| <HR> |
| <div class="chapter" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title"> |
| <a name="builtinfunctions-chapt"></a>Chapter 10. Built In Functions</h2> |
| </div> |
| <div> |
| <div class="authorgroup"> |
| <div class="author"> |
| <h3 class="author"> |
| <span class="firstname">Fred</span> <span class="surname">Toussi</span> |
| </h3> |
| <div class="affiliation"> |
| <span class="orgname">The HSQL Development Group<br> |
| </span> |
| </div> |
| </div> |
| </div> |
| </div> |
| <div> |
| <p class="releaseinfo">$Revision: 3601 $</p> |
| </div> |
| <div> |
| <div class="legalnotice"> |
| <a name="N12B56"></a> |
| <p>Copyright 2010 Fred Toussi. Permission is granted to distribute |
| this document without any alteration under the terms of the HSQLDB |
| license. Additional permission is granted to the HSQL Development Group |
| to distribute this document with or without alterations under the terms |
| of the HSQLDB license.</p> |
| </div> |
| </div> |
| <div> |
| <p class="pubdate">$Date: 2010-05-31 20:17:47 -0400 (Mon, 31 May 2010) $</p> |
| </div> |
| </div> |
| </div> |
| <div class="toc"> |
| <p> |
| <b>Table of Contents</b> |
| </p> |
| <dl> |
| <dt> |
| <span class="section"><a href="builtinfunctions-chapt.html#builtin_functions_intro-sect">Overview</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="builtinfunctions-chapt.html#builtin_functions_string-sect">String and Binary String Functions</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="builtinfunctions-chapt.html#builtin_functions_numeric-sect">Numeric Functions</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="builtinfunctions-chapt.html#builtin_functions_datetime-sect">Date Time and Interval Functions</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="builtinfunctions-chapt.html#N132C8">Array Functions</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="builtinfunctions-chapt.html#N132FD">General Functions</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="builtinfunctions-chapt.html#builtin_functions_sysfunc-sect">System Functions</a></span> |
| </dt> |
| </dl> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title" style="clear: both"> |
| <a name="builtin_functions_intro-sect"></a>Overview</h2> |
| </div> |
| </div> |
| </div> |
| <p>HyperSQL supports a wide range of built-in functions and allows |
| user-defined functions written in SQL and Java languages. User defined |
| functions are covered in a separate chapter. If a built-in function is not |
| available, you can write your own using SQL. Aggregate functions are |
| discussed in chapters that cover SQL in general.</p> |
| <p>The built-in functions fall into three groups:</p> |
| <p> |
| <div class="itemizedlist"> |
| <ul type="disc"> |
| <li> |
| <p>SQL Standard Functions</p> |
| <p>A wide rang of functions defined by SQL/Foundation are |
| supported. SQL/Foundation functions that have no parameter are |
| called without empty parentheses. Functions with multiple parameters |
| often use keywords instead of commas to separate the parameters. |
| Many functions are overloaded. Among these, some have one or more |
| optional parameters that can be omitted, while the return type of |
| some functions is dependent upon the type of one of the parameters. |
| The usage of SQL Standard Functions (where they can be used) is |
| covered more extensively in the <a class="link" href="dataaccess-chapt.html" title="Chapter 7. Data Access and Change">Data Access and Change</a> chapter</p> |
| </li> |
| <li> |
| <p>JDBC Open Group CLI Functions</p> |
| <p>These functions were defined as an extension to the CLI |
| standard, which is the basis for ODBC and JDBC and supported by many |
| database products. JDBC supports an escape mechanism to specify |
| function calls in SQL statements in a manner that is independent of |
| the function names supported by the target database engine. For |
| example <code class="literal">SELECT {fn DAYOFMONTH (dateColumn)} FROM |
| myTable</code> can be used in JDBC and is translated to Standard |
| SQL as <code class="literal">SELECT EXTRACT (DAY_OF_MONTH FROM dateColumn) FROM |
| myTable</code> if a database engine supports the Standard syntax. |
| If a database engine does not support Standard SQL, then the |
| translation will be different. HyperSQL supports all the function |
| names specified in the JDBC specifications as native functions. |
| Therefore, there is no need to use the <code class="literal">{fn FUNC_NAME ( ... |
| ) }</code> escape with HyperSQL. If a JDBC function is supported |
| by the SQL Standard in a different form, the SQL Standard form is |
| the preferred form to use.</p> |
| </li> |
| <li> |
| <p>HyperSQL Built-In Functions</p> |
| <p>Several additional built-in functions are available for some |
| useful operations. Some of these functions return the current |
| setting for the session and the database. The General Functions |
| accept arguments of different types and return values based on |
| comparison between the arguments.</p> |
| </li> |
| </ul> |
| </div> |
| </p> |
| <p>In the BNF specification used here, words in capital letters are |
| actual tokens. Syntactic elements such as expressions are enclosed in |
| angle brackets. The <code class="literal"><left paren></code> and |
| <code class="literal"><right paren></code> tokens are represented with the |
| actual symbol. Optional elements are enclosed with square brackets ( |
| <code class="literal"><left bracket></code> and <code class="literal"><right |
| bracket></code> ). Multiple options for a required element are |
| enclosed with braces (<code class="literal"> <left brace></code> and |
| <code class="literal"><right brace></code> )<code class="literal">.</code> Alternative |
| tokens are separated with the vertical bar ( <code class="literal"><vertical |
| bar></code> ). At the end of each function definition, the standard |
| which specifies the function is noted in parentheses as JDBC or HyperSQL, |
| unless the function is in the SQL/Foundation part of the SQL |
| Standard.</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title" style="clear: both"> |
| <a name="builtin_functions_string-sect"></a>String and Binary String Functions</h2> |
| </div> |
| </div> |
| </div> |
| <p>In SQL, there are three kinds of string: character, binary and bit. |
| The units are respectively characters, octets, and bits. Each kind of |
| string can be in different data types. CHAR, VARCHAR and CLOB are the |
| character data types. BINARY, VARBINARY and BLOB are the binary data |
| types. BIT and BIT VARYING are the bit string types. In all string |
| functions, the position of a unit of the string within the whole string is |
| specified from 1 to the length of the whole string. In the BNF, |
| <code class="literal"><char value expr> </code>indicates any valid SQL |
| expression that evaluates to a character type. Likewise, |
| <code class="literal"><binary value expr> </code>indicates a binary type |
| and<code class="literal"> <num value expr> </code>indicates a numeric |
| type.</p> |
| <a name="N12BA8" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>ASCII</strong></span> |
| </p> |
| <p> |
| <code class="literal">ASCII ( <char value expr> )</code> |
| </p> |
| <p>Returns an INTEGER equal to the ASCII code value of the first |
| character of <code class="literal"><char value expr></code>. (JDBC)</p> |
| <p> |
| <code class="literal">CHAR ( <UNICODE code> ) </code> |
| </p> |
| <p>The argument is an INTEGER. Returns a character string containing a |
| single character that has the specified<code class="literal"> <UNICODE |
| code></code>, which is an integer. ASCII codes are a subset of the |
| allowed values for <code class="literal"><UNICODE code></code>. (JDBC)</p> |
| <a name="N12BC4" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>CONCAT</strong></span> |
| </p> |
| <p> |
| <code class="literal">CONCAT ( <char value expr 1>, <char value expr |
| 2> )</code> |
| </p> |
| <p> |
| <code class="literal">CONCAT ( <binary value expr 1>, <binary value expr |
| 2> )</code> |
| </p> |
| <p>The arguments are character strings or binary strings. Returns a |
| string formed by concatenation of the arguments. Equivalent to the SQL |
| concatenation expression <code class="literal"><value expr 1> || <value expr |
| 2></code>. (JDBC)</p> |
| <a name="N12BD8" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>DIFFERENCE</strong></span> |
| </p> |
| <p> |
| <code class="literal">DIFFERENCE ( <char value expr 1>, <char value expr |
| 2> )</code> |
| </p> |
| <p>The arguments are character strings. Converts the arguments into |
| SOUNDEX codes, and returns an INTEGER between 0-4 which indicates how |
| similar the two SOUNDEX value are. If the values are the same, it returns |
| 4, if the values have no similarity, it returns 0. In-between values are |
| returned for partial similarity. (JDBC)</p> |
| <a name="N12BE6" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>INSERT</strong></span> |
| </p> |
| <p> |
| <code class="literal">INSERT ( <char value expr 1>, <offset>, |
| <length>, <char value expr 2> )</code> |
| </p> |
| <p>Returns a character string based on <code class="literal"><char value expr |
| 1></code> in which <code class="literal"><length></code> characters have |
| been removed from the <code class="literal"><offset></code> position and in |
| their place, the whole <code class="literal"><char value expr 2></code> is |
| copied. Equivalent to SQL/Foundation <code class="literal">OVERLAY( <char value |
| expr1> PLACING < char value expr2> FROM <offset> FOR |
| <length> )</code> . (JDBC)</p> |
| <a name="N12C03" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>HEXTORAW</strong></span> |
| </p> |
| <p> |
| <code class="literal">HEXTORAW( <char value expr> )</code> |
| </p> |
| <p>Returns a BINARY string formed by translation of hexadecimal digits |
| and letters in the <<code class="literal">char value expr></code>. Each |
| character of the <code class="literal"><char value expr></code> must be a |
| digit or a letter in the A | B | C | D | E | F set. Each byte of the |
| retired binary string is formed by translating two hex digits into one |
| byte. (HyperSQL)</p> |
| <a name="N12C17" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>LCASE</strong></span> |
| </p> |
| <p> |
| <code class="literal">LCASE ( <char value expr> ) </code> |
| </p> |
| <p>Returns a character string that is the lower case version of the |
| <code class="literal"><char value expr></code>. Equivalent to SQL/Foundation |
| <code class="literal">LOWER (<char value expr>)</code>. (JDBC)</p> |
| <a name="N12C2B" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>LEFT</strong></span> |
| </p> |
| <p> |
| <code class="literal">LEFT ( <char value expr>, <length> ) |
| </code> |
| </p> |
| <p>Returns a character string consisting of the first |
| <code class="literal"><length></code> characters of <code class="literal"><char value |
| expr></code>. Equivalent to SQL/Foundation<code class="literal"> |
| SUBSTRING(<char value expr> FROM 0 FOR <length>)</code>. |
| (JDBC)</p> |
| <a name="N12C42" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>LENGTH</strong></span> |
| </p> |
| <p> |
| <code class="literal">LENGTH ( <char value expr> ) </code> |
| </p> |
| <p>Returns as a BIGINT value the number of characters in |
| <code class="literal"><char value expr></code>. Equivalent to SQL/Foundation |
| <code class="literal">CHAR_LENGTH(<char value expr>)</code>. (JDBC)</p> |
| <a name="N12C56" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>LOCATE</strong></span> |
| </p> |
| <p> |
| <code class="literal">LOCATE ( <char value expr 1>, <char value expr |
| 2> [ , <offset> ] ) </code> |
| </p> |
| <p>Returns as a BIGINT value the starting position of the first |
| occurrence of <code class="literal"><char value expr 1></code> within |
| <code class="literal"><char value expr 2></code>. If |
| <code class="literal"><offset</code>> is specified, the search begins with the |
| position indicated by <code class="literal"><offset></code>. If the search is |
| not successful, 0 is returned. Equivalent to SQL/Foundation |
| <code class="literal">POSITION(<char value expr 1> IN <char value expr |
| 2>)</code>. (JDBC)</p> |
| <a name="N12C73" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>LTRIM</strong></span> |
| </p> |
| <p> |
| <code class="literal">LTRIM ( <char value expr> ) </code> |
| </p> |
| <p>Returns a character string based on <code class="literal"><char value |
| expr></code> with the leading space characters removed. Equivalent |
| to SQL/Foundation <code class="literal">TRIM( LEADING ' ' FROM <char value expr> |
| )</code>. (JDBC)</p> |
| <a name="N12C87" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>RAWTOHEX</strong></span> |
| </p> |
| <p> |
| <code class="literal">RAWTOHEX( <binary value expr> )</code> |
| </p> |
| <p>Returns a character string composed of hexadecimal digits |
| representing the bytes in the <code class="literal"><binary value |
| expr></code>. Each byte of the <code class="literal"><binary value |
| expr></code> is translated into two hex digits. (HyperSQL)</p> |
| <a name="N12C9B" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>REGEXP_MATCHES</strong></span> |
| </p> |
| <p> |
| <code class="literal">REGEXP_MATCHES ( <char value expr>, <regular |
| expression> ) </code> |
| </p> |
| <p>Returns true if the <char value expr> matches the <regular |
| expression>. The <regular expression> is defined according to |
| Java language rules. (HyperSQL)</p> |
| <a name="N12CA9" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>REPEAT</strong></span> |
| </p> |
| <p> |
| <code class="literal">REPEAT ( <char value expr>, <count> ) |
| </code> |
| </p> |
| <p>Returns a character string based on<code class="literal"> <char value |
| expr></code>, repeated <code class="literal"><count></code> times. |
| (JDBC)</p> |
| <a name="N12CBD" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>REPLACE</strong></span> |
| </p> |
| <p> |
| <code class="literal">REPLACE ( <char value expr 1>, <char value expr |
| 2>, <char value expr 3> )</code> |
| </p> |
| <p>Returns a character string based on <code class="literal"><char value expr |
| 1></code> where each occurrence of <code class="literal"><char value expr |
| 2></code> has been replaced with a copy of <code class="literal"><char value |
| expr 3></code>. (JDBC)</p> |
| <a name="N12CD4" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>REVERSE</strong></span> |
| </p> |
| <p> |
| <code class="literal">REVERSE ( <char value expr> )</code> |
| </p> |
| <p>Returns a character string based on <code class="literal"><char value |
| expr></code> with characters in the reverse order. (HyperSQL)</p> |
| <a name="N12CE5" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>RIGHT</strong></span> |
| </p> |
| <p> |
| <code class="literal">RIGHT ( <char value expr>, <count> ) |
| </code> |
| </p> |
| <p>Returns a character string consisting of the last |
| <code class="literal"><count></code> characters of <code class="literal"><char value |
| expr></code>. (JDBC)</p> |
| <a name="N12CF9" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>RTRIM</strong></span> |
| </p> |
| <p> |
| <code class="literal">RTRIM ( <char value expr> ) </code> |
| </p> |
| <p>Returns a character string based on <code class="literal"><char value |
| expr></code> with the trailing space characters removed. Equivalent |
| to SQL/Foundation <code class="literal">TRIM(TRAILING ' ' FROM <character |
| string>)</code>. (JDBC)</p> |
| <a name="N12D0D" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>SOUNDEX</strong></span> |
| </p> |
| <p> |
| <code class="literal">SOUNDEX ( <char value expr> ) </code> |
| </p> |
| <p>Returns a four character code representing the sound of |
| <code class="literal"><char value expr></code>. The US census algorithm is |
| used. For example the soundex value for Washington is W252. (JDBC)</p> |
| <a name="N12D1E" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>SPACE</strong></span> |
| </p> |
| <p> |
| <code class="literal">SPACE ( <count> ) </code> |
| </p> |
| <p>Returns a character string consisting of <code class="literal"><count> |
| </code>spaces. (JDBC)</p> |
| <a name="N12D2F" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>SUBSTR</strong></span> |
| </p> |
| <p> |
| <code class="literal">{ SUBSTR | SUBSTRING } ( <char value expr>, |
| <offset>, <length> )</code> |
| </p> |
| <p>The JDBC version of SQL/Foundation <code class="literal">SUBSTRING</code> |
| returns a character string that consists of |
| <code class="literal"><length></code> characters from <code class="literal"><char value |
| expr> </code>starting at the <code class="literal"><offset></code> |
| position. (JDBC)</p> |
| <a name="N12D49" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>UCASE</strong></span> |
| </p> |
| <p> |
| <code class="literal">UCASE ( <char value expr> ) </code> |
| </p> |
| <p>Returns a character string that is the lower case version of the |
| <code class="literal"><char value expr></code>. Equivalent to SQL/Foundation |
| <code class="literal">UPPER( <char value expr> )</code> . (JDBC)</p> |
| <a name="N12D5D" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>CHARACTER_LENGTH</strong></span> |
| </p> |
| <p> |
| <code class="literal">{ CHAR_LENGTH | CHARACTER_LENGTH } ( <char value |
| expression> [ USING { CHARACTERS | OCTETS } ] )</code> |
| </p> |
| <a name="N12D69" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>OCTET_LENGTH</strong></span> |
| </p> |
| <p> |
| <code class="literal">OCTET_LENGTH ( <string value expression> |
| )</code> |
| </p> |
| <a name="N12D75" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>BIT_LENGTH</strong></span> |
| </p> |
| <p> |
| <code class="literal">BIT_LENGTH ( <string value expression> |
| )</code> |
| </p> |
| <p>The CHAR_LENGTH or CHARACTER_LENGTH function can be used with |
| character strings, while OCTET_LENGTH can be used with character or binary |
| strings and BIT_LENGTH can be used with character, binary and bit |
| strings.</p> |
| <p>All functions return a BIGINT value that measures the length of the |
| string in the given unit. CHAR_LENGTH counts characters, OCTET_LENGTH |
| counts octets and BIT_LENGTH counts bits in the string. For CHAR_LENGTH, |
| if <code class="literal">[ USING OCTETS ] </code>is specified, the octet count is |
| returned. (Foundation)</p> |
| <a name="N12D88" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>OVERLAY</strong></span> |
| </p> |
| <p> |
| <code class="literal">OVERLAY ( <char value expr 1> PLACING <char value |
| expr 2></code> |
| </p> |
| <p> |
| <code class="literal">FROM <start position> [ FOR <string length> ] [ |
| USING CHARACTERS ] )</code> |
| </p> |
| <p> |
| <code class="literal">OVERLAY ( <binary value expr 1> PLACING <binary |
| value expr 2></code> |
| </p> |
| <p> |
| <code class="literal">FROM <start position> [ FOR <string length> ] |
| )</code> |
| </p> |
| <p>The character version of OVERLAY returns a character string based on |
| <code class="literal"><char value expr 1></code> in which <code class="literal"><string |
| length></code> characters have been removed from the |
| <code class="literal"><start position></code> and in their place, the whole |
| <code class="literal"><char value expr 2></code> is copied.</p> |
| <p>The binary version of OVERLAY returns a binary string formed in the |
| same manner as the character version. (Foundation)</p> |
| <a name="N12DAD" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>POSITION</strong></span> |
| </p> |
| <p> |
| <code class="literal">POSITION ( <char value expr 1> IN <char value expr |
| 2> [ USING CHARACTERS ] )</code> |
| </p> |
| <p> |
| <code class="literal">POSITION ( <binary value expr 1> IN <binary value |
| expr 2> )</code> |
| </p> |
| <p>The character and binary versions of POSITION search the string |
| value of the second argument for the first occurrence of the first |
| argument string. If the search is successful, the position in the string |
| is returned as a BIGINT. Otherwise zero is returned.</p> |
| <a name="N12DBE" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>SUBSTRING</strong></span> |
| </p> |
| <p> |
| <code class="literal">SUBSTRING ( <char value expr> FROM <start |
| position> [ FOR <string length> ] [ USING CHARACTERS ] |
| )</code> |
| </p> |
| <p> |
| <code class="literal">SUBSTRING ( <binary value expr> FROM <start |
| position> [ FOR <string length> ] )</code> |
| </p> |
| <p>The character version of SUBSTRING returns a character string that |
| consists of the characters of the <code class="literal"><char value expr> |
| </code>from <code class="literal"><start position></code>. If the |
| optional<code class="literal"> <string length></code> is specified, only |
| <code class="literal"><string length> </code>characters are returned.</p> |
| <p>The binary version of SUBSTRING returns a binary string in the same |
| manner. (Foundation)</p> |
| <a name="N12DDD" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>TRIM</strong></span> |
| </p> |
| <p> |
| <code class="literal">TRIM ([ [ LEADING | TRAILING | BOTH ] [ <trim |
| character> ] FROM ] <char value expr> )</code> |
| </p> |
| <p> |
| <code class="literal">TRIM ([ [ LEADING | TRAILING | BOTH ] [ <trim octet> |
| ] FROM ] <binary value expr> )</code> |
| </p> |
| <p>The character version of TRIM returns a character string based on |
| <code class="literal"><char value expr></code>. Consecutive instances of |
| <code class="literal"><trim character> </code>are removed from the beginning, |
| the end or both ends of the<code class="literal"><char value expr> |
| </code>depending on the value of the optional first qualifier |
| <code class="literal">[ LEADING | TRAILING | BOTH ]</code>. If no qualifier is |
| specified, <code class="literal">BOTH </code>is used as default. If <code class="literal">[ |
| <trim character> ]</code> is not specified, the space character |
| is used as default.</p> |
| <p>The binary version of TRIM returns a binary string based on |
| <code class="literal"><binary value expr></code>. Consecutive instances of |
| <code class="literal"><trim octet> </code>are removed in the same manner as in |
| the character version. If<code class="literal"> [ <trim octet> ]</code> is not |
| specified, the 0 octet is used as default. (Foundation)</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title" style="clear: both"> |
| <a name="builtin_functions_numeric-sect"></a>Numeric Functions</h2> |
| </div> |
| </div> |
| </div> |
| <a name="N12E0F" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>ABS</strong></span> |
| </p> |
| <p> |
| <code class="literal">ABS ( <num value expr> | <interval value expr> |
| ) </code> |
| </p> |
| <p>Returns the absolute value of the argument as a value of the same |
| type. (JDBC and Foundation)</p> |
| <a name="N12E1D" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>ACOS</strong></span> |
| </p> |
| <p> |
| <code class="literal">ACOS ( <num value expr> ) </code> |
| </p> |
| <p>Returns the arc-cosine of the argument in radians as a value of |
| DOUBLE type. (JDBC)</p> |
| <a name="N12E2B" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>ASIN</strong></span> |
| </p> |
| <p> |
| <code class="literal">ASIN ( <num value expr> ) </code> |
| </p> |
| <p>Returns the arc-sine of the argument in radians as a value of DOUBLE |
| type. (JDBC)</p> |
| <a name="N12E39" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>ATAN</strong></span> |
| </p> |
| <p> |
| <code class="literal">ATAN ( <num value expr> ) </code> |
| </p> |
| <p>Returns the arc-tangent of the argument in radians as a value of |
| DOUBLE type. (JDBC)</p> |
| <a name="N12E47" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>ATAN2</strong></span> |
| </p> |
| <p> |
| <code class="literal">ATAN2 ( <num value expr 1>, <num value expr 2> |
| ) </code> |
| </p> |
| <p>The <code class="literal"><num value expr 1></code> and <code class="literal"><num |
| value expr 2></code> express the <code class="varname">x</code> and |
| <code class="varname">y</code> coordinates of a point. Returns the angle, in |
| radians, representing the angle coordinate of the point in polar |
| coordinates, as a value of DOUBLE type. (JDBC)</p> |
| <a name="N12E61" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>CEILING</strong></span> |
| </p> |
| <p> |
| <code class="literal">{ CEIL | CEILING } ( <num value expr> ) |
| </code> |
| </p> |
| <p>Returns the smallest integer greater than or equal to the argument. |
| If the argument is exact numeric then the result is exact numeric with a |
| scale of 0. If the argument is approximate numeric, then the result is of |
| DOUBLE type. (JDBC and Foundation)</p> |
| <a name="N12E6F" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>BITAND</strong></span> |
| </p> |
| <p> |
| <code class="literal">BITAND ( <num value expr 1>, <num value expr 2> |
| )</code> |
| </p> |
| <p> |
| <code class="literal">BITAND ( <bit value expr 1>, <bit value expr 2> |
| )</code> |
| </p> |
| <a name="N12E7E" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>BITOR</strong></span> |
| </p> |
| <p> |
| <code class="literal">BITOR ( <num value expr 1>, <num value expr 2> |
| )</code> |
| </p> |
| <p> |
| <code class="literal">BITOR ( <bit value expr 1>, <bit value expr 2> |
| )</code> |
| </p> |
| <a name="N12E8D" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>BITXOR</strong></span> |
| </p> |
| <p> |
| <code class="literal">BITXOR ( <num value expr 1>, <num value expr 2> |
| )</code> |
| </p> |
| <p> |
| <code class="literal">BITXOR ( <bit value expr 1>, <bit value expr 2> |
| )</code> |
| </p> |
| <p>These three functions perform the bit operations: OR, AND, XOR, on |
| two values. The values are either integer values, or bit strings. The |
| result is an integer value of the same type as the arguments, or a bit |
| string of the same length as the argument. Each bit of the result is |
| formed by performing the operation on corresponding bits of the arguments. |
| (HyperSQL)</p> |
| <a name="N12E9E" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>COS</strong></span> |
| </p> |
| <p> |
| <code class="literal">COS ( <num value expr> ) </code> |
| </p> |
| <p>Returns the cosine of the argument (an angle expressed in radians) |
| as a value of DOUBLE type. (JDBC)</p> |
| <a name="N12EAC" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>COT</strong></span> |
| </p> |
| <p> |
| <code class="literal">COT ( <num value expr> ) </code> |
| </p> |
| <p>Returns the cotangent of the argument as a value of DOUBLE type. The |
| <code class="literal"><num value expr></code> represents an angle expressed in |
| radians. (JDBC)</p> |
| <a name="N12EBD" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>DEGREES</strong></span> |
| </p> |
| <p> |
| <code class="literal">DEGREES ( <num value expr> ) </code> |
| </p> |
| <p>Converts the argument (an angle expressed in<code class="literal"> |
| radians</code>) into degrees and returns the value in the DOUBLE type. |
| (JDBC)</p> |
| <a name="N12ECE" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>EXP</strong></span> |
| </p> |
| <p> |
| <code class="literal">EXP ( <num value expr> ) </code> |
| </p> |
| <p>Returns the exponential value of the argument as a value of DOUBLE |
| type. (JDBC and Foundation)</p> |
| <a name="N12EDC" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>FLOOR</strong></span> |
| </p> |
| <p> |
| <code class="literal">FLOOR ( <num value expr> ) </code> |
| </p> |
| <p>Returns the largest integer that is less than or equal to the |
| argument. If the argument is exact numeric then the result is exact |
| numeric with a scale of 0. If the argument is approximate numeric, then |
| the result is of DOUBLE type. (JDBC and Foundation)</p> |
| <a name="N12EEA" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>LN</strong></span> |
| </p> |
| <p> |
| <code class="literal">LN ( <num value expr> ) </code> |
| </p> |
| <p>Returns the natural logarithm of the argument, as a value of DOUBLE |
| type. (Foundation)</p> |
| <a name="N12EF8" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>LOG</strong></span> |
| </p> |
| <p> |
| <code class="literal">LOG ( <num value expr> ) </code> |
| </p> |
| <p>Returns the natural logarithm of the argument, as a value of DOUBLE |
| type. (JDBC)</p> |
| <a name="N12F06" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>LOG10</strong></span> |
| </p> |
| <p> |
| <code class="literal">LOG10 ( <num value expr> ) </code> |
| </p> |
| <p>Returns the base 10 logarithm of the argument as a value of DOUBLE |
| type. (JDBC)</p> |
| <p> |
| <code class="literal">MOD ( <num value expr 1>, <num value expr 2> ) |
| </code> |
| </p> |
| <a name="N12F17" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>MOD</strong></span> |
| </p> |
| <p>Returns the remainder (modulus) of <code class="literal"><num value expr |
| 1></code> divided by <code class="literal"><num value expr 2>.</code> |
| The data type of the returned value is the same as the second argument. |
| (JDBC and Foundation)</p> |
| <a name="N12F28" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>PI</strong></span> |
| </p> |
| <p> |
| <code class="literal">PI () </code> |
| </p> |
| <p>Returns the constant pi as a value of DOUBLE type. (JDBC)</p> |
| <a name="N12F36" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>POWER</strong></span> |
| </p> |
| <p> |
| <code class="literal">POWER ( <num value expr 1>, <num value expr 2> |
| ) </code> |
| </p> |
| <p>Returns the value of <code class="literal"><num value expr 1></code> |
| raised to the power of <code class="literal"><int value expr 2></code> as a |
| value of DOUBLE type. (JDBC and Foundation)</p> |
| <a name="N12F4A" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>RADIANS</strong></span> |
| </p> |
| <p> |
| <code class="literal">RADIANS ( <num value expr> ) </code> |
| </p> |
| <p>Converts the argument (an angle expressed in<code class="literal"> |
| degrees</code>) into radians and returns the value in the DOUBLE type. |
| (JDBC)</p> |
| <a name="N12F5B" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>RAND</strong></span> |
| </p> |
| <p> |
| <code class="literal">RAND ( [ <int value expr> ] ) </code> |
| </p> |
| <p>Returns a random value in the DOUBLE type. The optional <code class="literal">[ |
| <int value expr> ]</code> is used as seed value. In HyperSQL each |
| session has a separate random number generator. The first call that uses a |
| seed parameter sets the seed for subsequent calls that do not include a |
| parameter. (JDBC)</p> |
| <a name="N12F6C" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>ROUND</strong></span> |
| </p> |
| <p> |
| <code class="literal">ROUND ( <num value expr>, <int value expr> ) |
| </code> |
| </p> |
| <p>The <code class="literal"><num value expr> </code>is of the DOUBLE type. |
| The function returns a DOUBLE value which is the value of the argument |
| rounded to <code class="literal"><int value expr></code> places right of the |
| decimal point. If <code class="literal"><int value expr></code> is negative, |
| the first argument is rounded to <code class="literal"><int value expr></code> |
| places to the left of the decimal point. (JDBC)</p> |
| <a name="N12F86" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>SIGN</strong></span> |
| </p> |
| <p> |
| <code class="literal">SIGN ( <num value expr> ) </code> |
| </p> |
| <p>Returns an INTEGER, indicating the sign of the argument. If the |
| argument is negative then -1 is returned. If it is equal to zero then 0 is |
| returned. If the argument is positive then 1 is returned. (JDBC)</p> |
| <a name="N12F94" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>SIN</strong></span> |
| </p> |
| <p> |
| <code class="literal">SIN ( <num value expr> ) </code> |
| </p> |
| <p>Returns the sine of the argument (an angle expressed in radians) as |
| a value of DOUBLE type. (JDBC)</p> |
| <a name="N12FA2" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>SQRT</strong></span> |
| </p> |
| <p> |
| <code class="literal">SQRT ( <num value expr> ) </code> |
| </p> |
| <p>Returns the square root of the argument as a value of DOUBLE type. |
| (JDBC and Foundation)</p> |
| <a name="N12FB0" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>TAN</strong></span> |
| </p> |
| <p> |
| <code class="literal">TAN ( <num value expr> ) </code> |
| </p> |
| <p>Returns the tangent of the argument (an angle expressed in radians) |
| as a value of DOUBLE type. (JDBC)</p> |
| <a name="N12FBE" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>TRUNCATE</strong></span> |
| </p> |
| <p> |
| <code class="literal">TRUNCATE ( <num value expr>, <int value expr> ) |
| </code> |
| </p> |
| <p>Returns a value in the same type as <code class="literal"><num value |
| expr></code>. The value is rounded by replacing digits with zeros |
| from <code class="literal"><int value expr></code> places right of the decimal |
| point to the end. If <code class="literal"><int value expr></code> is |
| negative, <code class="literal">ABS( <int value expr> )</code> digits to left |
| of the decimal point and all digits to the right of the decimal points are |
| replaced with zeros. Results of calling TRUNCATE with 12345.6789 with (-2, |
| 0, 2, 4) are (12300.0000, 12345.0000, 12345.6700, 12345.6789). |
| (JDBC)</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title" style="clear: both"> |
| <a name="builtin_functions_datetime-sect"></a>Date Time and Interval Functions</h2> |
| </div> |
| </div> |
| </div> |
| <a name="N12FDC" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>TIMEZONE</strong></span> |
| </p> |
| <p> |
| <code class="literal">TIMEZONE()</code> |
| </p> |
| <p>Returns the current time zone for the session. Returns an INTERVAL |
| HOUR TO MINUTE value. (HyperSQL)</p> |
| <a name="N12FEA" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>SESSION_TIMEZONE</strong></span> |
| </p> |
| <p> |
| <code class="literal">SESSION_TIMEZONE()</code> |
| </p> |
| <p>Returns the default time zone for the current session. Returns an |
| INTERVAL HOUR TO MINUTE value. (HyperSQL)</p> |
| <a name="N12FF8" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>DATABASE_TIMEZONE</strong></span> |
| </p> |
| <p> |
| <code class="literal">DATABASE_TIMEZONE()</code> |
| </p> |
| <p>Returns the time zone for the database engine. This is based on |
| where the database server process is located. Returns an INTERVAL HOUR TO |
| MINUTE value. (HyperSQL)</p> |
| <a name="N13006" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>EXTRACT</strong></span> |
| </p> |
| <p> |
| <code class="literal">EXTRACT ( <extract field> FROM <extract source> |
| )</code> |
| </p> |
| <p> |
| <code class="literal"><extract field> ::= YEAR | MONTH | DAY | HOUR | |
| MINUTE | DAY_OF_WEEK | WEEK_OF_YEAR | QUARTER | DAY_OF_YEAR | DAY_OF_MONTH |
| |</code> |
| </p> |
| <p> |
| <code class="literal">TIMEZONE_HOUR | TIMEZONE_MINUTE | SECOND | |
| SECONDS_SINCE_MIDNIGHT |</code> |
| </p> |
| <p> |
| <code class="literal">DAY_NAME | MONTH_NAME</code> |
| </p> |
| <p> |
| <code class="literal"><extract source> ::= <datatime value expr> | |
| <interval value expr></code> |
| </p> |
| <p>The EXTRACT function returns a field or element of the |
| <code class="literal"><extract source></code>. The <code class="literal"><extract |
| source></code> is a datetime or interval expression. The type of the |
| return value is BIGINT for most of the<code class="literal"> <extract |
| field></code> options. The exceptions is <code class="literal">SECOND |
| </code>where a DECIMAL value is returned which has the same precision |
| as the datetime or interval expression. The field values <code class="literal">DAY_NAME |
| </code>or<code class="literal"> MONTH_NAME </code>result in a character string. |
| When <code class="literal">MONTH_NAME</code> is specified, a string in the range |
| January - December is returned. When <code class="literal">DAY_NAME </code>is |
| specified, a string in the range Sunday -Saturday is returned.</p> |
| <p>If the <code class="literal"><extract source></code> is <code class="literal">FROM |
| <datatime value expr></code>, different groups of |
| <code class="literal"><extract source></code> can be used depending on the |
| data type of the expression. The <code class="literal">TIMEZONE_HOUR | |
| TIMEZONE_MINUTE</code> options are valid only for TIME WITH TIMEZONE |
| and TIMESTAMP WITH TIMEZONE data types. The <code class="literal">HOUR | MINUTE | |
| SECOND | SECONDS_MIDNIGHT</code> options, are valid for TIME and |
| TIMESTAMP types. The rest of the fields are valid for DATE and TIMESTAMP |
| types.</p> |
| <p>If the <code class="literal"><extract source></code> is <code class="literal">FROM |
| <interval value expr></code>, the <code class="literal"><extract |
| field></code> must be one of the fields of the INTERVAL type of the |
| expressions. The <code class="literal">YEAR | MONTH</code> options may be valid for |
| INTERVAL types based on months. The <code class="literal">DAY | HOUR | MINUTE | SECOND |
| | SECONDS_MIDNIGHT</code> options may be valid for INTERVAL types based |
| on seconds. For example,<code class="literal"> DAY | HOUR | MINUTE</code> are the |
| only valid fields for the INTERVAL DAY TO MINUTE data type. (Foundation |
| with HyperSQL extensions)</p> |
| <a name="N1305D" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>CURRENT_DATE</strong></span> |
| </p> |
| <p> |
| <code class="literal">CURRENT_DATE</code> |
| </p> |
| <a name="N13069" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>CURRENT_TIME</strong></span> |
| </p> |
| <p> |
| <code class="literal">CURRENT_TIME [ ( <time precision> ) |
| ]</code> |
| </p> |
| <a name="N13075" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>LOCALTIME</strong></span> |
| </p> |
| <p> |
| <code class="literal">LOCALTIME [ ( <time precision> ) ]</code> |
| </p> |
| <a name="N13081" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>CURRENT_TIMESTAMP</strong></span> |
| </p> |
| <p> |
| <code class="literal">CURRENT_TIMESTAMP [ ( <timestamp precision> ) |
| ]</code> |
| </p> |
| <a name="N1308D" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>LOCALTIMESTAMP</strong></span> |
| </p> |
| <p> |
| <code class="literal">LOCALTIMESTAMP [ ( <timestamp precision> ) |
| ]</code> |
| </p> |
| <p>These datetime functions return the datetime value representing the |
| moment the function is called. CURRENT_DATE returns a value of DATE type. |
| CURRENT_TIME returns a value of TIME WITH TIME ZONE type. LOCALTIME |
| returns a value of TIME type. CURRENT_TIMESTAMP returns a value of |
| TIMESTAMP WITH TIME ZONE type. LOCALTIMESTAMP returns a value of TIMESTAMP |
| type. If the optional <code class="literal">[ ( <time precision> ) ]</code> |
| or<code class="literal"> [ ( <timestamp precision> ) ]</code> is used, then |
| the returned value has the specified fraction of the second precision. |
| (Foundation)</p> |
| <a name="N130A1" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>CURDATE</strong></span> |
| </p> |
| <p> |
| <code class="literal">CURDATE ()</code> |
| </p> |
| <p>This function is equivalent to<code class="literal"> CURRENT_DATE. |
| </code>(JDBC)</p> |
| <a name="N130B2" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>CURTIME</strong></span> |
| </p> |
| <p> |
| <code class="literal">CURTIME ()</code> |
| </p> |
| <p>This function is equivalent to<code class="literal"> LOCALTIME</code>. |
| (JDBC)</p> |
| <a name="N130C3" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>DAYNAME</strong></span> |
| </p> |
| <p> |
| <code class="literal">DAYNAME ( <datatime value expr> )</code> |
| </p> |
| <p>This function is equivalent to<code class="literal"> EXTRACT ( DAY_NAME FROM ... |
| ) </code>Returns a string in the range of Sunday - Saturday. |
| (JDBC)</p> |
| <a name="N130D4" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>DAYOFMONTH</strong></span> |
| </p> |
| <p> |
| <code class="literal">DAYOFMONTH ( <datetime value expr> )</code> |
| </p> |
| <p>This function is equivalent to<code class="literal"> EXTRACT ( DAY_OF_MONTH FROM |
| ... ) </code>Returns an integer value in the range of 1-31. |
| (JDBC)</p> |
| <a name="N130E5" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>DAYOFWEEK</strong></span> |
| </p> |
| <p> |
| <code class="literal">DAYOFWEEK ( <datetime value expr> )</code> |
| </p> |
| <p>This function is equivalent to <code class="literal">EXTRACT ( DAY_OF_WEEK FROM |
| ... ) </code>Returns an integer value in the range of 1-7. The first |
| day of the week is Sunday. (JDBC)</p> |
| <a name="N130F6" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>DAYOFYEAR</strong></span> |
| </p> |
| <p> |
| <code class="literal">DAYOFYEAR ( <datetime value expr> )</code> |
| </p> |
| <p>This function is equivalent to <code class="literal">EXTRACT ( DAY_OF_YEAR FROM |
| ... ) </code>Returns an integer value in the range of 1-366. |
| (JDBC)</p> |
| <a name="N13107" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>HOUR</strong></span> |
| </p> |
| <p> |
| <code class="literal">HOUR ( <datetime value expr> )</code> |
| </p> |
| <p>This function is equivalent to <code class="literal">EXTRACT ( HOUR FROM ... ) |
| </code>Returns an integer value in the range of 0-23. (JDBC)</p> |
| <a name="N13118" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>MINUTE</strong></span> |
| </p> |
| <p> |
| <code class="literal">MINUTE ( <datetime value expr> )</code> |
| </p> |
| <p>This function is equivalent to<code class="literal"> EXTRACT ( MINUTE FROM ... ) |
| </code>Returns an integer value in the range of 0 - 59. (JDBC)</p> |
| <a name="N13129" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>MONTH</strong></span> |
| </p> |
| <p> |
| <code class="literal">MONTH ( <datetime value expr> )</code> |
| </p> |
| <p>This function is equivalent to <code class="literal">EXTRACT ( MONTH FROM ... ) |
| </code>Returns an integer value in the range of 1-12. (JDBC)</p> |
| <a name="N1313A" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>MONTHNAME</strong></span> |
| </p> |
| <p> |
| <code class="literal">MONTHNAME ( <datetime value expr> )</code> |
| </p> |
| <p>This function is equivalent to <code class="literal">EXTRACT ( NAME_OF_MONTH FROM |
| ... ) </code>Returns a string in the range of January - December. |
| (JDBC)</p> |
| <a name="N1314B" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>NOW</strong></span> |
| </p> |
| <p> |
| <code class="literal">NOW ()</code> |
| </p> |
| <p>This function is equivalent to |
| <code class="literal">LOCAL_TIMESTAMP.</code> |
| </p> |
| <a name="N1315B" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>QUARTER</strong></span> |
| </p> |
| <p> |
| <code class="literal">QUARTER ( <datetime value expr> )</code> |
| </p> |
| <p>This function is equivalent to <code class="literal">EXTRACT ( QUARTER FROM ... ) |
| </code>Returns an integer in the range of 1 - 4. (JDBC)</p> |
| <a name="N1316C" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>SECOND</strong></span> |
| </p> |
| <p> |
| <code class="literal">SECOND ( <datetime value expr> )</code> |
| </p> |
| <p>This function is equivalent to <code class="literal">EXTRACT ( SECOND FROM ... ) |
| </code>Returns an integer or decimal in the range of 0 - 59, with the |
| same precision as the <datetime value expr>. (JDBC)</p> |
| <a name="N1317D" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>SECONDS_SINCE_MIDNIGHT</strong></span> |
| </p> |
| <p> |
| <code class="literal">SECONDS_SINCE_MIDNIGHT ( <datetime value expr> |
| )</code> |
| </p> |
| <p>This function is equivalent to<code class="literal"> EXTRACT ( |
| SECONDS_SINCE_MIDNIGHT FROM ... ) </code>Returns an integer in the |
| range of 0 - 86399. (HyperSQL)</p> |
| <a name="N1318E" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>WEEK</strong></span> |
| </p> |
| <p> |
| <code class="literal">WEEK ( <datetime value expr> )</code> |
| </p> |
| <p>This function is equivalent to<code class="literal"> EXTRACT ( WEEK_OF_YEAR FROM |
| ... ) </code>Returns an integer in the range of 1 - 54. (JDBC)</p> |
| <a name="N1319F" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>YEAR</strong></span> |
| </p> |
| <p> |
| <code class="literal">YEAR ( <datetime value expr> )</code> |
| </p> |
| <p>This function is equivalent to<code class="literal"> EXTRACT ( YEAR FROM ... ) |
| </code>Returns an integer in the range of 1 - 9999. (JDBC)</p> |
| <a name="N131B0" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>TIMESTAMPADD</strong></span> |
| </p> |
| <p> |
| <code class="literal">TIMESTAMPADD ( <tsi datetime field>, <numeric |
| value expression>, <datetime value expr>)</code> |
| </p> |
| <a name="N131BC" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>TIMESTAMPDIFF</strong></span> |
| </p> |
| <p> |
| <code class="literal">TIMESTAMPDIFF ( <tsi datetime field>, <datetime |
| value expr 1>, <datetime value expr 2>)</code> |
| </p> |
| <p> |
| <code class="literal"><tsi datetime field> ::= SQL_TSI_FRAC_SECOND | |
| SQL_TSI_SECOND | SQL_TSI_MINUTE | SQL_TSI_HOUR | SQL_TSI_DAY | |
| SQL_TSI_WEEK | SQL_TSI_MONTH | SQL_TSI_QUARTER | |
| SQL_TSI_YEAR</code> |
| </p> |
| <p>HyperSQL supports full SQL Standard datetime features. It supports |
| adding integers representing units of time directly to datetime values |
| using the arithmetic plus operator. It also supports subtracting one |
| <code class="literal"><datetime value expr></code> from another in the given |
| units of days using the minus operator. An example of |
| <code class="literal"><datetime value expr> + <numeric value expression> |
| <datetime field> </code>is <code class="literal">LOCAL_TIMESTAMP + 5 |
| DAY</code>. An example of <code class="literal">( <datetime value expr> - |
| <numeric value expression> ) <datetime field> </code>is |
| <code class="literal">(CURRENT_DATE - DATE '2008-08-8') MONTH </code>which returns |
| the number of calendar months between the two dates.</p> |
| <p>The two JDBC functions, <code class="literal">TIMESTAMPADD </code>and |
| <code class="literal">TIMESTAMPDIFF</code> perform the same function as above SQL |
| expressions. The field names are keywords and are different from those |
| used in the EXTRACT functions. These names are valid for use only when |
| calling these two functions. The return value for TIMESTAMPADD is of the |
| same type as the datetime argument used. The return type for TIMESTAMPDIFF |
| is always BIGINT, regardless of the type of arguments. The two datetime |
| arguments of TIMESTAMPDIFF should be of the same type. (JDBC)</p> |
| <a name="N131E4" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>DATEADD</strong></span> |
| </p> |
| <p> |
| <code class="literal">DATEADD ( <field>, <numeric value expr>, |
| <datetime value expr> )</code> |
| </p> |
| <a name="N131F0" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>DATEDIFF</strong></span> |
| </p> |
| <p> |
| <code class="literal">DATEDIFF ( <field>, <datetime value expr 1>, |
| datetime value expr 2> )</code> |
| </p> |
| <p> |
| <code class="literal"><field> ::= 'yy' | 'mm' | 'dd' | 'hh' | 'mi' | 'ss' | |
| 'ms'</code> |
| </p> |
| <p>The DATEADD and DATEDIFF functions are alternatives to TIMESTAMPADD |
| and TIMESTAMPDIFF, with fewer available field options. The field names are |
| specified as strings, rather than keywords. The fields translate to YEAR, |
| MONTH, DAY, HOUR, MINUTE, SECOND and MILLISECOND. (HyperSQL}</p> |
| <a name="N13201" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>TO_CHAR</strong></span> |
| </p> |
| <p> |
| <code class="literal">TO_CHAR( <datetime value expr>, <char value |
| expr> )</code> |
| </p> |
| <p>This function formats a datetime or numeric value to the format |
| specified by the pattern given in the second argument. The pattern can |
| contain pattern elements from the list given below, plus punctuation and |
| space characters. An example, including the result, is given below:</p> |
| <pre class="programlisting">TO_CHAR ( TIMESTAMP'2008-02-01 20:30:40', 'YYYY BC MONTH, DAY HH') |
| |
| 2008 AD February, Friday 8 |
| </pre> |
| <p>The format is internally translated to a |
| <code class="classname">java.text.SimpleDateFormat</code> format string. Any |
| character sequences not listed below are included in the Java format |
| string and may cause unexpected results or errors. Therefore unsupported |
| format strings should not be used. The supported format components are as |
| follows:</p> |
| <div class="table"> |
| <a name="N13216"></a> |
| <p class="title"> |
| <b>Table 10.1. TO CHAR Values</b> |
| </p> |
| <div class="table-contents"> |
| <table summary="TO CHAR Values" cellspacing="0" style="border-collapse: collapse;border-top: 0.5pt solid ; border-bottom: 0.5pt solid ; border-left: 0.5pt solid ; border-right: 0.5pt solid ; "> |
| <colgroup> |
| <col> |
| <col> |
| </colgroup> |
| <tbody> |
| <tr> |
| <td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; "><code class="literal">BC | B.C. | AD | A.D.</code></td><td style="border-bottom: 0.5pt solid ; ">Returns <code class="literal">AD</code> for common era and |
| <code class="literal">BC</code> for before common era</td> |
| </tr> |
| <tr> |
| <td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; "><code class="literal">RRRR</code></td><td style="border-bottom: 0.5pt solid ; "> |
| <p>4-digit year</p> |
| </td> |
| </tr> |
| <tr> |
| <td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; "><code class="literal">YYYY</code></td><td style="border-bottom: 0.5pt solid ; "> |
| <p>4-digit year</p> |
| </td> |
| </tr> |
| <tr> |
| <td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; "><code class="literal">IYYY</code></td><td style="border-bottom: 0.5pt solid ; "> |
| <p>4-digit year</p> |
| </td> |
| </tr> |
| <tr> |
| <td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; "><code class="literal">YY</code></td><td style="border-bottom: 0.5pt solid ; "> |
| <p>2 digit year</p> |
| </td> |
| </tr> |
| <tr> |
| <td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; "><code class="literal">IY</code></td><td style="border-bottom: 0.5pt solid ; "> |
| <p>2 digit year</p> |
| </td> |
| </tr> |
| <tr> |
| <td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; "><code class="literal">IYYY</code></td><td style="border-bottom: 0.5pt solid ; "> |
| <p>4-digit year</p> |
| </td> |
| </tr> |
| <tr> |
| <td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; "><code class="literal">MM</code></td><td style="border-bottom: 0.5pt solid ; "> |
| <p>Month (01-12)</p> |
| </td> |
| </tr> |
| <tr> |
| <td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; "><code class="literal">MON</code></td><td style="border-bottom: 0.5pt solid ; "> |
| <p>Short three-letter name of month</p> |
| </td> |
| </tr> |
| <tr> |
| <td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; "><code class="literal">MONTH</code></td><td style="border-bottom: 0.5pt solid ; "> |
| <p>Name of month</p> |
| </td> |
| </tr> |
| <tr> |
| <td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; "><code class="literal">WW</code></td><td style="border-bottom: 0.5pt solid ; "> |
| <p>Week of year (1-53) where week 1 starts on the first |
| day of the year and continues to the seventh day of the |
| year.</p> |
| </td> |
| </tr> |
| <tr> |
| <td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; "><code class="literal">W</code></td><td style="border-bottom: 0.5pt solid ; "> |
| <p>Week of month (1-5) where week 1 starts on the first |
| day of the month and ends on the seventh.</p> |
| </td> |
| </tr> |
| <tr> |
| <td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; "><code class="literal">IW</code></td><td style="border-bottom: 0.5pt solid ; "> |
| <p>Week of year (1-52 or 1-53) based on the ISO |
| standard.</p> |
| </td> |
| </tr> |
| <tr> |
| <td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; "><code class="literal">DAY</code></td><td style="border-bottom: 0.5pt solid ; "> |
| <p>Name of day.</p> |
| </td> |
| </tr> |
| <tr> |
| <td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; "><code class="literal">DD</code></td><td style="border-bottom: 0.5pt solid ; "> |
| <p>Day of month (1-31).</p> |
| </td> |
| </tr> |
| <tr> |
| <td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; "><code class="literal">DDD</code></td><td style="border-bottom: 0.5pt solid ; "> |
| <p>Day of year (1-366).</p> |
| </td> |
| </tr> |
| <tr> |
| <td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; "><code class="literal">DY</code></td><td style="border-bottom: 0.5pt solid ; "> |
| <p>Short three-letter name of day.</p> |
| </td> |
| </tr> |
| <tr> |
| <td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; "><code class="literal">HH</code></td><td style="border-bottom: 0.5pt solid ; "> |
| <p>Hour of day (0-11).</p> |
| </td> |
| </tr> |
| <tr> |
| <td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; "><code class="literal">HH12</code></td><td style="border-bottom: 0.5pt solid ; "> |
| <p>Hour of day (0-11).</p> |
| </td> |
| </tr> |
| <tr> |
| <td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; "><code class="literal">HH24</code></td><td style="border-bottom: 0.5pt solid ; "> |
| <p>Hour of day (0-23).</p> |
| </td> |
| </tr> |
| <tr> |
| <td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; "><code class="literal">MI</code></td><td style="border-bottom: 0.5pt solid ; "> |
| <p>Minute (0-59).</p> |
| </td> |
| </tr> |
| <tr> |
| <td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; "><code class="literal">SS</code></td><td style="border-bottom: 0.5pt solid ; "> |
| <p>Second (0-59).</p> |
| </td> |
| </tr> |
| <tr> |
| <td style="border-right: 0.5pt solid ; "><code class="literal">FF</code></td><td style=""> |
| <p>Fractional seconds.</p> |
| </td> |
| </tr> |
| </tbody> |
| </table> |
| </div> |
| </div> |
| <br class="table-break"> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title" style="clear: both"> |
| <a name="N132C8"></a>Array Functions</h2> |
| </div> |
| </div> |
| </div> |
| <p>Array functions are specialised functions with ARRAY |
| parameters.</p> |
| <a name="N132CD" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>CARDINALITY</strong></span> |
| </p> |
| <p> |
| <code class="literal">CARDINALITY( <array value expr> )</code> |
| </p> |
| <p>Returns the element count for the given array argument. |
| (Foundation)</p> |
| <a name="N132DB" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>MAX_CARDINALITY</strong></span> |
| </p> |
| <p> |
| <code class="literal">MAX_CARDINALITY( <array value expr> |
| )</code> |
| </p> |
| <p>Returns the maximum allowed element count for the given array |
| argument. (Foundation)</p> |
| <a name="N132E9" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>TRIM_ARRAY</strong></span> |
| </p> |
| <p> |
| <code class="literal">TRIM_ARRAY( <array value expr>, <num value |
| expr> )</code> |
| </p> |
| <p>Returns a new array that contains the elements of the |
| <code class="literal"><array value expr></code> minus the number of elements |
| specified by the <code class="literal"><num value expr>. </code>Elements are |
| discarded from the end of the array. (Foundation)</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title" style="clear: both"> |
| <a name="N132FD"></a>General Functions</h2> |
| </div> |
| </div> |
| </div> |
| <p>General functions can take different types of arguments. Some |
| General Functions accept a variable number of arguments.</p> |
| <a name="N13302" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>COALESCE</strong></span> |
| </p> |
| <p> |
| <code class="literal">COALESCE( <value expr 1>, <value expr 2> [, |
| ...] )</code> |
| </p> |
| <p>Returns <code class="literal"><value expr 1></code> if it is not null, |
| otherwise returns <code class="literal"><value expr 2></code> if not null and |
| so on. The type of both arguments must be comparable. (Foundation)</p> |
| <a name="N13316" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>CONVERT</strong></span> |
| </p> |
| <p> |
| <code class="literal">CONVERT ( <value expr> , <data type> |
| )</code> |
| </p> |
| <p> |
| <code class="literal"><data type> ::= { SQL_BIGINT | SQL_BINARY | SQL_BIT |
| |SQL_BLOB | SQL_BOOLEAN | SQL_CHAR | SQL_CLOB | SQL_DATE | SQL_DECIMAL | |
| SQL_DATALINK |SQL_DOUBLE | SQL_FLOAT | SQL_INTEGER | SQL_LONGVARBINARY | |
| SQL_LONGNVARCHAR | SQL_LONGVARCHAR | SQL_NCHAR | SQL_NCLOB | SQL_NUMERIC | |
| SQL_NVARCHAR | SQL_REAL | SQL_ROWID | SQL_SQLXML | SQL_SMALLINT | SQL_TIME |
| | SQL_TIMESTAMP | SQL_TINYINT | SQL_VARBINARY | SQL_VARCHAR} [ ( |
| <precision, length or scale parameters> ) ]</code> |
| </p> |
| <p>The CONVERT function is a JDBC escape function, equivalent to the |
| SQL standard CAST expression. It converts the <code class="literal"><value |
| expr></code> into the given <code class="literal"><data type></code> and |
| returns the value. The <code class="literal"><data type></code> options are |
| synthetic names made by prefixing type names with <code class="literal">SQL_</code>. |
| Some of the <code class="literal"><data type></code> options represent valid |
| SQL types, but some are based on non-standard type names, namely |
| <code class="literal">{ SQL_LONGNVARCHAR | SQL_LONGVARBINARY |SQL_LONGVARCHAR | |
| SQL_TINYINT }</code>. None of the synthetic names can be used in any |
| other context than the CONVERT function.</p> |
| <p>The definition of CONVERT in the JDBC Standard does not allow the |
| precision, scale or length to be specified. This is required by the SQL |
| standard for BINARY, BIT, BLOB, CHAR, CLOB, VARBINARY and VARCHAR types |
| and is often needed for DECIMAL and NUMERIC. Therefore, HyperSQL allows |
| the use of precision, scale or length for the type definition when they |
| are valid for the type definition. HyperSQL also allows the use of real |
| type names (without the <code class="literal">SQL_</code> prefix). (JDBC)</p> |
| <a name="N1333E" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>DECODE</strong></span> |
| </p> |
| <p> |
| <code class="literal">DECODE( <value expr main>, <value expr match |
| 1>, <value expr result 1> [...,] [, <value expr default>] |
| )</code> |
| </p> |
| <p>DECODE takes at least 3 arguments. The <code class="literal"><value expr |
| main></code> is compared with <code class="literal"><value expr match |
| 1></code> and if it matches, <code class="literal"><value expr result |
| 1></code> is returned. If there are additional pairs of |
| <code class="literal"><value expr match n></code> and <code class="literal"><value expr |
| result n></code>, comparison is repeated until a match is found the |
| result is returned. If no match is found, the <code class="literal"><value expr |
| default></code> is returned if it is specified, otherwise NULL is |
| returned. The type of the return value is a combination of the types of |
| the <code class="literal"><value expr result ... ></code> arguments. |
| (HyperSQL)</p> |
| <a name="N13361" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>GREATEST</strong></span> |
| </p> |
| <p> |
| <code class="literal">GREATEST( <value expr 1>, [<value expr ...>, |
| ...] )</code> |
| </p> |
| <p>The GREATEST function takes one or more arguments. It compares the |
| arguments with each other and returns the greatest argument. The return |
| type is the combined type of the arguments. Arguments can be of any type, |
| so long as they are comparable. (HyperSQL)</p> |
| <a name="N1336F" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>IFNULL</strong></span> |
| </p> |
| <p> |
| <code class="literal">IFNULL( <value expr 1>, <value expr 2> |
| )</code> |
| </p> |
| <p>Returns <code class="literal"><value expr 1></code> if it is not null, |
| otherwise returns <code class="literal"><value expr 2></code>. The type of |
| both arguments must be the same. Equivalent to SQL Standard |
| <code class="literal">COALESCE(<value expr 1>, <value expr 2>)</code> |
| function. (JDBC)</p> |
| <a name="N13386" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>LEAST</strong></span> |
| </p> |
| <p> |
| <code class="literal">LEAST( <value expr 1>, [<value expr ...>, ...] |
| )</code> |
| </p> |
| <p>The LEAST function takes one or more arguments. It compares the |
| arguments with each other and returns the smallest argument. The return |
| type is the combined type of the arguments. Arguments can be of any type, |
| so long as they are comparable. (HyperSQL)</p> |
| <a name="N13394" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>NULLIF</strong></span> |
| </p> |
| <p> |
| <code class="literal">NULLIF( <value expr 1>, <value expr 2> |
| )</code> |
| </p> |
| <p>Returns <code class="literal"><value expr 1></code> if it is not equal |
| to <code class="literal"><value expr 2></code>, otherwise returns null. The |
| type of both arguments must be the same. This function is a shorthand for |
| a specific CASE expression. (Foundation)</p> |
| <a name="N133A8" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>NVL</strong></span> |
| </p> |
| <p> |
| <code class="literal">NVL( <value expr 1>, <value expr 2> |
| )</code> |
| </p> |
| <p>Returns <code class="literal"><value expr 1></code> if it is not null, |
| otherwise returns <code class="literal"><value expr 2></code>. The type of the |
| return value is the combined type of the two value expressions. For |
| example, if <value expr 1> is an INTEGER column and |
| <code class="literal"><value expr 2></code> is a DOUBLE constant, the return |
| type is DOUBLE. This function is the same as IFNULL and COALESCE |
| (HyperSQL)</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title" style="clear: both"> |
| <a name="builtin_functions_sysfunc-sect"></a>System Functions</h2> |
| </div> |
| </div> |
| </div> |
| <a name="N133C3" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>CRYPT_KEY</strong></span> |
| </p> |
| <p> |
| <code class="literal">CRYPT_KEY( <value expr 1>, <value expr 2> |
| )</code> |
| </p> |
| <p>Returns a binary string representation of a cryptography key for the |
| given cipher and cyptography provider. The cipher specification is |
| specified by <code class="literal"><value expr 1></code> and the provider by |
| <code class="literal"><value expr 2></code>. To use the default provider, |
| specify null for <code class="literal"><value expr 2></code>. |
| (HyperSQL)</p> |
| <a name="N133DA" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>IDENTITY</strong></span> |
| </p> |
| <p> |
| <code class="literal">IDENTITY ()</code> |
| </p> |
| <p>Returns the last IDENTITY value inserted into a row by the current |
| session. The statement, CALL IDENTITY() can be made after an INSERT |
| statement that inserts a row into a table with an IDENTITY column. The |
| CALL IDENTITY() statement returns the last IDENTITY value that was |
| inserted into a table by the current session. Each session manages this |
| function call separately and is not affected by inserts in other sessions. |
| The statement can be executed as a direct statement or a prepared |
| statement. (HyperSQL)</p> |
| <a name="N133E8" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>DATABASE</strong></span> |
| </p> |
| <p> |
| <code class="literal">DATABASE ()</code> |
| </p> |
| <p>Returns the file name (without directory information) of the |
| database. (JDBC)</p> |
| <a name="N133F6" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>DATABASE_VERSION</strong></span> |
| </p> |
| <p> |
| <code class="literal">DATABASE_VERSION ()</code> |
| </p> |
| <p>Returns the full version string for the database engine. For |
| example, 2.0.1. (JDBC)</p> |
| <a name="N13404" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>USER</strong></span> |
| </p> |
| <p> |
| <code class="literal">USER ()</code> |
| </p> |
| <p>Equivalent to the SQL function <code class="literal">CURRENT_USER</code>. |
| (JDBC)</p> |
| <a name="N13415" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>CURRENT_USER</strong></span> |
| </p> |
| <p> |
| <code class="literal">CURRENT_USER</code> |
| </p> |
| <a name="N13421" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>CURRENT_ROLE</strong></span> |
| </p> |
| <p> |
| <code class="literal">CURRENT_ROLE</code> |
| </p> |
| <a name="N1342D" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>SESSION_USER</strong></span> |
| </p> |
| <p> |
| <code class="literal">SESSION_USER</code> |
| </p> |
| <a name="N13439" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>SYSTEM_USER</strong></span> |
| </p> |
| <p> |
| <code class="literal">SYSTEM_USER</code> |
| </p> |
| <a name="N13445" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>CURRENT_SCHEMA</strong></span> |
| </p> |
| <p> |
| <code class="literal">CURRENT_SCHEMA</code> |
| </p> |
| <a name="N13451" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>CURRENT_CATALOG</strong></span> |
| </p> |
| <p> |
| <code class="literal">CURRENT_CATALOG</code> |
| </p> |
| <p>These functions return the named current session attribute. They are |
| all SQL Standard functions.</p> |
| <p>The CURRENT_USER is the user that connected to the database, or a |
| user subsequently set by the SET AUTHORIZATION statement.</p> |
| <p>SESSION_USER is the same as CURRENT_USER</p> |
| <p>SYSTEM_USER is the user that connected to the database. It is not |
| changed with any command until the session is closed.</p> |
| <p>CURRENT_SCHEMA is default schema of the user, or a schema |
| subsequently set by the SET SCHEMA command.</p> |
| <p>CURRENT_CATALOG is always the same within a given HyperSQL database |
| and indicates the name of the catalog.</p> |
| <a name="N13469" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>ISAUTOCOMMIT</strong></span> |
| </p> |
| <p> |
| <code class="literal">ISAUTOCOMMIT()</code> |
| </p> |
| <p>Returns TRUE if the session is in autocommit mode. (HyperSQL)</p> |
| <a name="N13477" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>ISREADONLYSESSION</strong></span> |
| </p> |
| <p> |
| <code class="literal">ISREADONLYSESSION()</code> |
| </p> |
| <p>Returns TRUE if the session is in read only mode. (HyperSQL)</p> |
| <a name="N13485" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>ISREADONLYDATABASE</strong></span> |
| </p> |
| <p> |
| <code class="literal">ISREADONLYDATABASE()</code> |
| </p> |
| <p>Returns TRUE if the database is a read only database. |
| (HyperSQL)</p> |
| <a name="N13493" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>ISREADONLYDATABASEFILES</strong></span> |
| </p> |
| <p> |
| <code class="literal">ISREADONLYDATABASEFILES()</code> |
| </p> |
| <p>Returns TRUE if the database is a read-only files database. In this |
| kind of database, it is possible to modify the data, but the changes are |
| not persisted to the database files. (HyperSQL)</p> |
| <a name="N134A1" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>ISOLATION_LEVEL</strong></span> |
| </p> |
| <p> |
| <code class="literal">ISOLATION_LEVEL()</code> |
| </p> |
| <p>Returns the current transaction isolation level for the session. |
| Returns either READ COMMITTED or SERIALIZABLE as a string. |
| (HyperSQL)</p> |
| <a name="N134AF" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>SESSION_ISOLATION_LEVEL</strong></span> |
| </p> |
| <p> |
| <code class="literal">SESSION_ISOLATION_LEVEL()</code> |
| </p> |
| <p>Returns the default transaction isolation level for the current |
| session. Returns either READ COMMITTED or SERIALIZABLE as a string. |
| (HyperSQL)</p> |
| <a name="N134BD" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>DATABASE_ISOLATION_LEVEL</strong></span> |
| </p> |
| <p> |
| <code class="literal">DATABASE_ISOLATION_LEVEL()</code> |
| </p> |
| <p>Returns the default transaction isolation level for the database. |
| Returns either READ COMMITTED or SERIALIZABLE as a string. |
| (HyperSQL)</p> |
| <a name="N134CB" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>TRANSACTION_CONTROL</strong></span> |
| </p> |
| <p> |
| <code class="literal">TRANSACTION_CONTROL()</code> |
| </p> |
| <p>Returns the current transaction model for the database. Returns |
| LOCKS, MVLOCKS or MVCC as a string. (HyperSQL)</p> |
| </div> |
| </div> |
| <HR xmlns:xi="http://www.w3.org/2001/XInclude"> |
| <P xmlns:xi="http://www.w3.org/2001/XInclude" class="svnrev">$Revision: 3601 $</P> |
| <div class="navfooter"> |
| <hr> |
| <table summary="Navigation footer" width="100%"> |
| <tr> |
| <td align="left" width="40%"><a accesskey="p" href="triggers-chapt.html"><img src="../images/db/prev.png" alt="Prev"></a> </td><td align="center" width="20%"> </td><td align="right" width="40%"> <a accesskey="n" href="deployment-chapt.html"><img src="../images/db/next.png" alt="Next"></a></td> |
| </tr> |
| <tr> |
| <td valign="top" align="left" width="40%">Chapter 9. Triggers </td><td align="center" width="20%"><a accesskey="h" href="index.html"><img src="../images/db/home.png" alt="Home"></a></td><td valign="top" align="right" width="40%"> Chapter 11. System Management and Deployment |
| Issues</td> |
| </tr> |
| </table> |
| </div> |
| </body> |
| </html> |