 | Chapter 4. Built In Functions |  |
| Chapter 3. Schemas and Database Objects |  | Chapter 5. Data Access and Change |
| | Chapter 4. Built In Functions | |
| Chapter 3. Schemas and Database Objects | | Chapter 5. Data Access and Change |
$Revision: 6787 $
Copyright 2010-2024 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.
2024-10-25
Table of Contents
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 the SQL-Invoked Routines chapter. If a built-in function is not available, you can write your own using procedural SQL or Java.
Built-in aggregate functions such as SUM,
MAX, ARRAY_AGG,
GROUP_CONCAT are covered in the Data Access and Change chapter, which
covers SQL in general. SQL expressions such as
COALESCE, NULLIF and
CAST are also discussed there.
The built-in functions fall into three groups:
SQL Standard Functions
A wide range 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 Data Access and Change chapter
JDBC Open Group CLI Functions
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 SELECT {fn DAYOFMONTH (dateColumn)} FROM
myTable can be used in JDBC and is translated to Standard
SQL as SELECT EXTRACT (DAY_OF_MONTH FROM dateColumn) FROM
myTable 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 {fn FUNC_NAME ( ...
) } 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.
HyperSQL Built-In Functions
Many 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.
In the BNF specification used here, words in capital letters are
actual tokens. Syntactic elements such as expressions are enclosed in
angle brackets. The <left paren> and
<right paren> tokens are represented with the
actual symbol. Optional elements are enclosed with square brackets (
<left bracket> and <right
bracket> ). Multiple options for a required element are
enclosed with braces ( <left brace> and
<right brace> ). Alternative
tokens are separated with the vertical bar ( <vertical
bar> ). At the end of each function definition, the standard
which specifies the function is noted in parentheses as JDBC or HyperSQL,
or the SQL:2023 Standard.
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,
<char value expr> indicates any valid SQL
expression that evaluates to a character type. Likewise,
<binary value expr> indicates a binary type
and <num value expr> indicates a numeric
type.
ASCII
ASCII ( <char value expr> )
Returns an INTEGER equal to the ASCII code value of the first
character of <char value expr>. (JDBC)
ASCIISTR
ASCIISTR ( <char value expr> )
Returns the ASCII representation of the string argument with all characters outside the range 32-126 replaced with Unicode escape codes. (HyperSQL)
BIT_LENGTH
BIT_LENGTH ( <string value expression>
)
BIT_LENGTH can be used with character, binary and bit strings. It return a BIGINT value that measures the bit length of the string. (SQL:2023)
See also CHARACTER_LENGTH and OCTET_LENGTH.
CHAR
CHAR ( <UNICODE code> )
The argument is an INTEGER. Returns a character string containing a
single character that has the specified <UNICODE
code>, which is an integer. ASCII codes are a subset of the
allowed values for <UNICODE code>. (JDBC)
CHARACTER_LENGTH
{ CHAR_LENGTH | CHARACTER_LENGTH } ( <char value
expression> [ USING { CHARACTERS | OCTETS } ] )
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.
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 [ USING OCTETS ] is specified, the octet count is
returned, which is twice the normal length. (SQL:2023)
CONCAT
CONCAT ( <char value expr 1>, <char value expr
2> [, ...] )
CONCAT ( <binary value expr 1>, <binary value expr
2> [, ...] )
The arguments are character strings or binary strings. Returns a
string formed by concatenation of the arguments. Minimum number of
arguments is 2. Equivalent to the SQL concatenation expression
<value expr 1> || <value expr 2> [ || ...]
.
Handling of null values in the CONCAT function depends on the
database property sql.concat_nulls ( SET
DATABASE SQL SYNTAX CONCAT NULLS { TRUE || FALSE } ). By
default, any null value will cause the function to return null. If the
property is set false, then NULL values are replaced with empty
strings.
(JDBC)
CONCAT_WS
CONCAT_WS ( <char value separator>, <char value
expr 1>, <char value expr 2> [, ...] )
The arguments are character strings. Returns a string formed by
concatenation of the arguments from the second argument, using the
separator from the first argument. Minimum number of arguments is 3.
Equivalent to the SQL concatenation expression <value expr
1> || <separator> || <value expr 2> [ || ...] .
The function ignores null values and returns an empty string if all values
are null. It returns null only if the separator is null.
This function is similar to a MySQL function of the same name.
(HyperSQL)
DIFFERENCE
DIFFERENCE ( <char value expr 1>, <char value expr
2> )
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)
FROM_BASE64
FROM_BASE64( <character value expr>
)
Returns a binary string by converting from the base64
<character value expr>. (HyperSQL)
INSERT
INSERT ( <char value expr 1>, <offset>,
<length>, <char value expr 2> )
Returns a character string based on <char value expr
1> in which <length> characters have
been removed from the <offset> position and in
their place, the whole <char value expr 2> is
copied. Equivalent to SQL/Foundation OVERLAY( <char value
expr1> PLACING < char value expr2> FROM <offset> FOR
<length> ) . (JDBC)
INSTR
INSTR ( <char value expr 1>, <char value expr
2> [ , <offset> ] )
Returns as a BIGINT value the starting position of the first
occurrence of <char value expr 2> within
<char value expr 1>. If
<offset> is specified, the search begins with the
position indicated by <offset>. If the search is
not successful, 0 is returned. Similar to the LOCATE
function but the order of the arguments is reversed. (HyperSQL)
HEX
HEX( <binary value expr> )
HEX( <numeric value expr> )
Returns a character string of hexadecimal digits and letters
representing the <binary value expr>. Exactly the
same as the RAWTOHEX function. With
<numeric value expr> the hexadecimal digits
represent the number in base 16 (HyperSQL)
HEXTORAW
HEXTORAW( <char value expr> )
Returns a binary string formed by translation of hexadecimal digits
and letters in the <char value expr>. Each
character of the <char value expr> 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)
LCASE
LCASE ( <char value expr> )
Returns a character string that is the lower-case version of the
<char value expr>. Equivalent to SQL/Foundation
LOWER (<char value expr>). (JDBC)
LEFT
LEFT ( <char value expr>, <count> )
Returns a character string consisting of the first
<count> characters of <char value
expr>. Equivalent to SQL/Foundation
SUBSTRING(<char value expr> FROM 0 FOR <count>).
(JDBC)
LENGTH
LENGTH ( <char value expr> )
Returns as a BIGINT value the number of characters in
<char value expr>. Equivalent to SQL/Foundation
CHAR_LENGTH(<char value expr>). (JDBC)
LOCATE
LOCATE ( <char value expr 1>, <char value expr
2> [ , <offset> ] )
Returns as a BIGINT value the starting position of the first
occurrence of <char value expr 1> within
<char value expr 2>. If
<offset> is specified, the search begins with the
position indicated by <offset>. If the search is
not successful, 0 is returned. Without the third argument,
LOCATE is equivalent to the SQL Standard function
POSITION(<char value expr 1> IN <char value expr
2>). (JDBC)
LOWER
LOWER ( <char value expr> )
Returns a character string that is the lower-case version of the
<char value expr>. (SQL:2023)
LPAD
LPAD ( <char value expr 1>, <length> [,
<char value expr 2> ] )
Returns a character string with the length of
<length> characters. The string contains the
characters of <char value expr 1> padded to the
left with spaces. If <length> is smaller than the
length of the string argument, the argument is truncated. If the optional
<char value expr 2> is specified, this string is
used for padding, instead of spaces. (SQL:2023)
LTRIM
LTRIM ( <char value expr 1> [, <char value expr
2> ] )
When called with a single argument, returns a character string based
on <char value expr 1> with the leading space
characters removed. Equivalent to SQL/Foundation TRIM( LEADING '
' FROM <char value expr1> ). When called with two
arguments, <char value expr 2> represents the
leading character to be removed. (SQL:2023)
OCTET_LENGTH
OCTET_LENGTH ( <string value expression>
)
The OCTET_LENGTH function can be used with character or binary strings.
Return a BIGINT value that measures the length of the string in
octets. When used with character strings, the octet
count is returned, which is twice the normal length. (SQL:2023)
OVERLAY
OVERLAY ( <char value expr 1> PLACING <char value
expr 2>
FROM <start position> [ FOR <string length> ] [
USING CHARACTERS ] )
OVERLAY ( <binary value expr 1> PLACING <binary
value expr 2>
FROM <start position> [ FOR <string length> ]
)
The character version of OVERLAY returns a character string based on
<char value expr 1> in which <string
length> characters have been removed from the
<start position> and in their place, the whole
<char value expr 2> is copied.
The binary version of OVERLAY returns a binary string formed in the same manner as the character version. (SQL:2023)
POSITION
POSITION ( <char value expr 1> IN <char value expr
2> [ USING CHARACTERS ] )
POSITION ( <binary value expr 1> IN <binary value
expr 2> )
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. (SQL:2023)
RAWTOHEX
RAWTOHEX( <binary value expr> )
Returns a character string composed of hexadecimal digits
representing the bytes in the <binary value
expr>. Each byte of the <binary value
expr> is translated into two hex digits. (HyperSQL)
REGEXP_COUNT
REGEXP_COUNT ( <char value expr>, <regular
expression> )
Returns as an INTEGER value the number of regions of the
<char value expr> that match the
<regular expression>. The <regular
expression> is defined according to Java language regular
expression rules. Returns 0 if no match is found. (HyperSQL)
REGEXP_INSTR
REGEXP_INSTR ( <char value expr>, <regular
expression> )
Returns as an INTEGER value the starting position of the first
region of the <char value expr> that matches the
<regular expression>. The <regular
expression> is defined according to Java language regular
expression rules. Returns 0 if no match is found. (HyperSQL)
REGEXP_LIKE
REGEXP_LIKE ( <char value expr>, <regular
expression> )
REGEXP_MATCHES
REGEXP_MATCHES ( <char value expr>, <regular
expression> )
Both functions return true if the <char value
expr> matches the <regular
expression> as a whole. The <regular
expression> is defined according to Java language regular
expression rules. (HyperSQL)
REGEXP_REPLACE
REGEXP_REPLACE ( <char value expr>, <regular
expression> [, <replace char value expr> [, <start
position> [, <replace count> [, <options>]]]] )
Replaces <char value expr 1> regions that
match the <regular expression> with
<replace char value expr>. This last parameter is
optional and defaults to the empty string. The rest of the parameters are
also optional. The <start position> parameter is
not implemented and must be 1 if used. The <replace
count> parameter is 0 by default, which means replace all
occurrences, or it can be 1, which means replace only the first
occurrence. The <options> parameter is a string
which can contain: 'i' for case-insensitive compare, 'c' for Unicode case,
'n' for the '.' also to match any line terminator, and 'm' for multi-line
matches. The <regular expression> is defined
according to Java language regular expression rules. (HyperSQL)
REGEXP_SUBSTR
REGEXP_SUBSTR ( <char value expr>, <regular
expression> )
REGEXP_SUBSTRING
REGEXP_SUBSTRING ( <char value expr>, <regular
expression> )
Both functions return the first region in the <char
value expr> that matches the <regular
expression>. The <regular
expression> is defined according to Java language regular
expression rules. (HyperSQL)
REGEXP_SUBSTRING_ARRAY
REGEXP_SUBSTRING_ARRAY ( <char value expr>,
<regular expression> )
Returns all the regions in the <char value
expr> that match the <regular
expression>. The <regular
expression> is defined according to Java language regular
expression rules. Returns an array containing the matching regions
(HyperSQL)
REPEAT
REPEAT ( <char value expr>, <count> )
Returns a character string based on <char value
expr>, repeated <count> times.
(JDBC)
REPLACE
REPLACE ( <char value expr 1>, <char value expr
2> [, <char value expr 3> ] )
Returns a character string based on <char value expr
1> where each occurrence of <char value expr
2> has been replaced with a copy of <char value
expr 3>. If the function is called with just two arguments,
the <char value expr 3> defaults to the empty string and calling the
function simply removes the occurrences of <char value expr
2> from the first string.(JDBC)
REVERSE
REVERSE ( <char value expr> )
Returns a character string based on <char value
expr> with characters in the reverse order. (HyperSQL)
RIGHT
RIGHT ( <char value expr>, <count> )
Returns a character string consisting of the last
<count> characters of <char value
expr>. (JDBC)
RPAD
RPAD ( <char value expr 1>, <length> [,
<char value expr 2> ] )
Returns a character string with the length of
<length> characters. The string begins with the
characters of <char value expr 1> padded to the
right with spaces. If <length> is smaller than
the length of the string argument, the argument is truncated. If the
optional <char value expr 2> is specified, this
string is used for padding, instead of spaces. (SQL:2023)
RTRIM
RTRIM ( <char value expr 1> [, <char value expr
2> ] )
When called with a single argument, returns a character string based
on <char value expr 1> with the trailing space
characters removed. Equivalent to SQL/Foundation TRIM(TRAILING '
' FROM <character string>). When called with two
arguments, <char value expr 2> represents the
trailing character to be removed. (SQL:2023)
SOUNDEX
SOUNDEX ( <char value expr> )
Returns a four-character code representing the sound of
<char value expr>. The US census algorithm is
used. For example, the soundex value for "Washington" is W252. This
function is used for storing the soundex of names in a column of a table,
then searching this column for strings matching the soundex of the search
string, then comparing the actual names stored in the database with the
search string. (JDBC)
SPACE
SPACE ( <count> )
Returns a character string consisting of <count>
spaces. (JDBC)
SUBSTR
{ SUBSTR | SUBSTRING } ( <char value expr>,
<offset>, <length> )
The JDBC version of SQL/Foundation SUBSTRING
returns a character string that consists of
<length> characters from <char value
expr> starting at the <offset>
position. (JDBC)
SUBSTRING
SUBSTRING ( <char value expr> FROM <start
position> [ FOR <string length> ] [ USING CHARACTERS ]
)
SUBSTRING ( <binary value expr> FROM <start
position> [ FOR <string length> ] )
The character version of SUBSTRING returns a character string that
consists of the characters of the <char value expr>
from <start position>. If the
optional <string length> is specified, only
<string length> characters are returned.
The binary version of SUBSTRING returns a binary string in the same manner. (SQL:2023)
TO_BASE64
TO_BASE64( <binary value expr> )
Returns a character string as a base 64 representation of the bytes
in the <binary value expr>. (HyperSQL)
TO_CHAR
TO_CHAR ( <Numeric value expr>, <char value
expr> )
This function converts a numeric value to a character string using the format given in the second argument. In the format template, the digits 0 and 9 are used for the digits of the numeric value. If the numeric value has more significant digits than the template, the hash symbol (#) is returned as the result. By default, the result has the minus sign at the beginning when the numeric value is negative, or a space when it is positive. The special format element B can be used at the beginning of the format template to return a blank string when the numeric value is zero. The special format element MI can be used at the end of the format template for a minus sign for negative numeric values or a space for positive values. The special format element S can be used at the beginning or end of the template for a plus or minus sign to be included for all numeric values at that position. Alternatively, the format element PR can be used once at the end of the format string for angle brackets around the negative results. Numeric values of type DOUBLE are first converted to DECIMAL with the same scale as indicated in the template. (HyperSQL)
Table 4.1. TO_CHAR (number) format elements
9 | The digit 9 means a space is used in the result when no digit is present. Otherwise the digit from the numeric value. |
0 |
The digit 0 means the zero digit is used in the result when no digit is present. Otherwise the digit from the numeric value. |
B |
A blank string is returned if the numeric value is zero. |
FM |
Removes the initial spaces in the result. |
MI |
Space or minus sign at the end of the sequence of digits. |
S |
Plus or minus sign at the beginning or end of the sequence of digits. |
PR |
Angle brackets around the result are used for negative values. |
. |
The period character. Used for the decimal point. |
, |
The comma character. Used for grouping digits. |
TO_CHAR ( 1234567.890, '999,999,999.999' )
1,234,567.890
TO_CHAR ( 1234567.890, '000,999,999.999' )
001,234,567.890
TRIM
TRIM ([ [ LEADING | TRAILING | BOTH ] [ <trim
character> ] FROM ] <char value expr> )
TRIM ([ [ LEADING | TRAILING | BOTH ] [ <trim octet>
] FROM ] <binary value expr> )
The character version of TRIM returns a character string based on
<char value expr>. Consecutive instances of
<trim character> are removed from the beginning,
the end or both ends of the<char value expr>
depending on the value of the optional first qualifier
[ LEADING | TRAILING | BOTH ]. If no qualifier is
specified, BOTH is used as default. If [
<trim character> ] is not specified, the space character
is used as default.
The binary version of TRIM returns a binary string based on
<binary value expr>. Consecutive instances of
<trim octet> are removed in the same manner as in
the character version. If [ <trim octet> ] is not
specified, the 0 octet is used as default. (SQL:2023)
TRANSLATE
TRANSLATE( <char value expr1>, <char value
expr2>, <char value expr3> )
Returns a character string based on <char value
expr1> source. Each character of the source is checked
against the characters in <char value expr2>. If
the character is not found, it is not modified. If the character is found,
then the character in the same position in <char value
expr3> is used. If <char value
expr2> is longer than <char value
expr3>, then those characters at the end that have no
counterpart in <char value expr3> are dropped
from the result. (HyperSQL)
-- in this example any accented character in acolumn is replaced with one without an accent TRANSLATE( acolumn, 'ÁÇÉÍÓÚÀÈÌÒÙÂÊÎÔÛÃÕËÜáçéíóúàèìòùâêîôûãõëü', 'ACEIOUAEIOUAEIOUAOEUaceiouaeiouaeiouaoeu');
UCASE
UCASE ( <char value expr> )
Returns a character string that is the upper case version of the
<char value expr>. Equivalent to SQL/Foundation
UPPER( <char value expr> ). (JDBC)
UPPER
UPPER ( <char value expr> )
Returns a character string that is the upper case version of the
<char value expr> . (SQL:2023)
UNHEX
UNHEX( <char value expr> )
Returns a binary string formed by translation of hexadecimal digits
and letters in the <char value expr>. Exactly the
same as the HEXTORAW function. (HyperSQL)
UNISTR
UNISTR( <char value expr> )
Returns a string formed by translation of hexadecimal escape
sequences in the <char value expr> to UTF-16
characters. Exactly the opposite of ASCIISTR function.
(HyperSQL)
JSON constructor functions convert SQL data into JSON values, These functions are supported by HyperSQL according to the SQL:2023 Standard. Each function returns a JSON object or JSON array as a string. The format conforms to the IETF rfc:7159 document, The JavaScript Object Notation (JSON) Data Interchange Format.
In the BNF, <JSON value expr> indicates any
valid SQL expression that evaluates to a single value. This includes
strings, numbers and booleans. Binary values are represented as
hexadecimal strings. All other types, including dates and timestamps, are
represented as strings. An optional FORMAT JSON is used
when the result of the value expression is already a string in the JSON
format.
<JSON value expr> ::= <value expression> [
FORMAT JSON ]
The optional <JSON constructor null clause>
indicates whether NULL values are represented as JSON nulls or are omitted
from the result. The default behaviour is ABSENT ON
NULL for JSON_ARRAY, and NULL ON
NULL for JSON_OBJECT.
<JSON null clause> ::= NULL ON NULL | ABSENT ON
NULL
The optional <JSON output clause> allows
you to change the maximum length of the returned string from the default
32K bytes.
<JSON output clause> ::= RETURNING
VARCHAR(N)
JSON function calls can be nested to construct more complex objects containing arrays, objects, and arrays of objects.
JSON_ARRAY
JSON_ARRAY ( <JSON value expr>, ... [<JSON null
caluse>] [<JSON output caluse>])
The first form of JSON_ARRAY is a variable
argument function, It converts the values from a series of value
expression into a single JSON array.
JSON_ARRAY ( <query expression> [ FORMAT JSON ]
[<JSON null caluse>] [<JSON output caluse>])
The second form of JSON_ARRAY converts the values
from a single <query expression> into a JSON array.
(SQL:2023)
-- a JSON array is constructed from a single row of the table SELECT JSON_ARRAY( country_id , name, local_name ) FROM places.countries WHERE country_id = 'ESP' C1 ------------------------ ["ESP","Spain","España"] -- a JSON array is constructed from all rows of the table VALUES JSON_ARRAY(SELECT name FROM countries) C1 --------------------------------------------------------------------- ["Austria","Switzerland","Germany","Spain","France","Italy","Sweden"]
JSON_ARRAYAGG
JSON_ARRAYAGG ( <JSON value expr> [<order by
clause>] [<JSON null caluse>] )
JSON_ARRAYAGG is an aggregate function similar to
the ARRAY_AGG SQL function, It evaluates a value
expression over a series of rows and combines the values, separated by
commas, into a single JSON array. Returns a JSON string. (SQL:2023)
-- a JSON array is constructed from all rows of the table SELECT JSON_ARRAYAGG( country_id ) FROM places.countries C1 ------------------------------------------- ["AUT","CHE","DEU","ESP","FRA","ITA","SWE"]
JSON_OBJECT
JSON_OBJECT ( <JSON name and value>, ... [<JSON
null caluse>] [ { WITH | WITHOUT } UNIQUE [ KEYS] ] )
JSON_OBJECT is a variable argument function, It
combines a series of keys and values into a single JSON object. Returns a
JSON string.
There are two supported syntax forms for key:value pairs.
<JSON name and value> ::= <JSON name>
<colon> <JSON value expression> | [ KEY ] <JSON name>
VALUE <JSON value expression>
The optional WITH UNIQUE KEYS clause forces an
error if the keys within the JSON object are not unique. The default is
WITHOUT UNIQUE KEYS. (SQL:2023)
-- a JSON object is constructed from each row of the table; both syntax options are shown
SELECT country_id, JSON_OBJECT( country_id : name ) FROM places.countries
SELECT country_id, JSON_OBJECT( KEY country_id VALUE name ) FROM places.countries
COUNTRY_ID C2
---------- ---------------------
AUT {"AUT":"Austria"}
CHE {"CHE":"Switzerland"}
DEU {"DEU":"Germany"}
ESP {"ESP":"Spain"}
FRA {"FRA":"France"}
ITA {"ITA":"Italy"}
SWE {"SWE":"Sweden"}
JSON_OBJECTAGG
JSON_OBJECTAGG ( <JSON name and value> [<JSON null
caluse>] [ { WITH | WITHOUT } UNIQUE [ KEYS] ] )
JSON_OBJECTAGG is an aggregate function similar
to the ARRAY_AGG SQL function, It constructs a JSON
object by using keys and values from the aggregated rows.
(SQL:2023)
-- a JSON object is constructed from multiple rows of the table
SELECT JSON_OBJECTAGG( country_id : name ) FROM places.countries
C1
---------------------------------------------------------------------------------------------------------------
{"AUT":"Austria","CHE":"Switzerland","DEU":"Germany","ESP":"Spain","FRA":"France","ITA":"Italy","SWE":"Sweden"}
ABS
ABS ( <num value expr> | <interval value expr>
)
Returns the absolute value of the argument as a value of the same type. (SQL:2023)
ACOS
ACOS ( <num value expr> )
Returns the arc-cosine of the argument in radians as a value of DOUBLE type. (SQL:2023)
ASIN
ASIN ( <num value expr> )
Returns the arc-sine of the argument in radians as a value of DOUBLE type. (SQL:2023)
ATAN
ATAN ( <num value expr> )
Returns the arc-tangent of the argument in radians as a value of DOUBLE type. (SQL:2023)
ATAN2
ATAN2 ( <num value expr 1>, <num value expr 2>
)
The <num value expr 1> and <num
value expr 2> express the x and
y 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)
CEILING
{ CEIL | CEILING } ( <num value expr> )
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. (SQL:2023)
BITAND
BITAND ( <num value expr 1>, <num value expr 2>
)
BITAND ( <bit value expr 1>, <bit value expr 2>
)
BITANDNOT
BITANDNOT ( <num value expr 1>, <num value expr
2> )
BITANDNOT ( <bit value expr 1>, <bit value expr
2> )
BITNOT
BITNOT ( <num value expr 1> )
BITNOT ( <bit value expr 1> )
BITOR
BITOR ( <num value expr 1>, <num value expr 2>
)
BITOR ( <bit value expr 1>, <bit value expr 2>
)
BITXOR
BITXOR ( <num value expr 1>, <num value expr 2>
)
BITXOR ( <bit value expr 1>, <bit value expr 2>
)
These functions perform bit operations on two values, or in the case of BITNOT on a single value. 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. The names of the function indicate NOT, OR, AND, XOR operations. The BITANDNOT performs NOT on the second argument, then performs AND on result and the first argument. (HyperSQL)
COS
COS ( <num value expr> )
Returns the cosine of the argument (an angle expressed in radians) as a value of DOUBLE type. (SQL:2023)
COSH
COSH ( <num value expr> )
Returns the hyperbolic cosine of the argument as a value of DOUBLE type. (SQL:2023)
COT
COT ( <num value expr> )
Returns the cotangent of the argument as a value of DOUBLE type. The
<num value expr> represents an angle expressed in
radians. (SQL:2023)
DEGREES
DEGREES ( <num value expr> )
Converts the argument (an angle expressed in
radians) into degrees and returns the value in the DOUBLE type.
(JDBC)
EXP
EXP ( <num value expr> )
Returns the exponential value of the argument as a value of DOUBLE type. (SQL:2023)
FLOOR
FLOOR ( <num value expr> )
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. (SQL:2023)
LN
LN ( <num value expr> )
Returns the natural logarithm of the argument, as a value of DOUBLE type. (SQL:2023)
LOG
LOG ( <num value expr> )
Returns the natural logarithm of the argument, as a value of DOUBLE type. (JDBC)
LOG10
LOG10 ( <num value expr> )
Returns the base 10 logarithm of the argument as a value of DOUBLE type. (SQL:2023)
MOD
MOD ( <num value expr 1>, <num value expr 2> )
Returns the remainder (modulus) of <num value expr
1> divided by <num value expr 2>.
The data type of the returned value is the same as the second argument.
(SQL:2023)
NANVL
NANVL ( <num value expr 1>, <num value expr 2>
)
Returns an alternative for the NaN (Not a Number) double value in
<num value expr 1> as <num value expr
2>., otherwise returns the first argument. The data type of
the returned value is DOUBLE. (HyperSQL)
PI
PI ()
Returns the constant pi as a value of DOUBLE type. (JDBC)
POWER
POWER ( <num value expr 1>, <num value expr 2>
)
Returns the value of <num value expr 1>
raised to the power of <int value expr 2> as a
value of DOUBLE type. (SQL:2023)
RADIANS
RADIANS ( <num value expr> )
Converts the argument (an angle expressed in
degrees) into radians and returns the value in the DOUBLE type.
(JDBC)
RAND
RAND ( [ <int value expr> ] )
Returns a random value in the DOUBLE type. The optional [
<int value expr> ] 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)
ROUND
ROUND ( <num value expr>, <int value expr> )
The <num value expr> is of the DOUBLE type
or DECIMAL type. The function returns a DOUBLE or DECIMAL value which is
the value of the argument rounded to <int value
expr> places right of the decimal point. If <int
value expr> is negative, the first argument is rounded to
<int value expr> places to the left of the
decimal point.
This function rounds values ending with .5 or larger away from zero for DECIMAL arguments and results. When the value ends with .5 or larger and the argument and result are DOUBLE, It rounds the value towards the closest even value.
The datetime version is discussed in the next section. (JDBC)
SIGN
SIGN ( <num value expr> )
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)
SIN
SIN ( <num value expr> )
Returns the sine of the argument (an angle expressed in radians) as a value of DOUBLE type. (SQL:2023)
SINH
SINH ( <num value expr> )
Returns the hyperbolic sine of the argument as a value of DOUBLE type. (SQL:2023)
SQRT
SQRT ( <num value expr> )
Returns the square root of the argument as a value of DOUBLE type. (SQL:2023)
TAN
TAN ( <num value expr> )
Returns the tangent of the argument (an angle expressed in radians) as a value of DOUBLE type. (SQL:2023)
TANH
TANH ( <num value expr> )
Returns the hyperbolic tangent of the argument as a value of DOUBLE type. (SQL:2023)
TO_NUMBER
TO_NUMBER ( <char value expr> )
Performs a cast from character to DECIMAL number. The character string must consist of digits and can have a decimal point. Use the SQL Standard CAST expression instead of this non-standard function. (HyperSQL)
TRUNC
TRUNC ( <num value expr> [, <int value expr>] )
This is a similar to the TRUNCATE function when
the first argument is numeric. If the second argument is omitted, zero is
used in its place.
The datetime version is discussed in the next section. (HyperSQL)
TRUNCATE
TRUNCATE ( <num value expr> [, <int value
expr>] )
Returns a value in the same type as <num value
expr> but may reduce the scale of DECIMAL and NUMERIC values.
The value is rounded by replacing digits with zeros from <int
value expr> places right of the decimal point to the end. If
<int value expr> is negative, ABS(
<int value expr> ) 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,
12345, 12345.67, 12345.6789). The function does not change the number if
the second argument is larger than or equal to the scale of the first
argument.
If the second argument is not a constant (when it is a parameter or column reference) then the type of the return value is always the same as the type of the first argument. In this case, the discarded digits are replaced with zeros. (JDBC)
WIDTH_BUCKET
WIDTH_BUCKET ( <value expr 1> , <value expr 2>,
<value expr 3>, <int value expr> )
Returns an integer value between 0 and <int value
expr> + 1. The initial three parameters are of the same
numeric or datetime type. The range, ( <value expr 2> ,
<value expr 3> ) is divided into <int value
expr> equal sections (buckets). The returned integer value
indicates the index of the bucket where <value expr
1> can be placed. If the <value expr
1> falls before or after the range, the return value is 0 or
<value expr 1> + 1 respectively.
This function can be used with numeric or datetime values. Invalid
arguments, including <int value expr> smaller
than 1, or equal values for <value expr 2> and
<value expr 3> will cause an exception.
(SQL:2023)
An example is given below:
WIDTH_BUCKET( 5, 10, 110, 10) 0 WIDTH_BUCKET( 23, 10, 110, 10) 2 WIDTH_BUCKET( 100, 10, 110, 10) 10 WIDTH_BUCKET( 200, 10, 110, 10) 11
Functions to report the time zone.
TIMEZONE
TIMEZONE()
Returns the current time zone displacement for the session. This value is the same as SESSION_TIMEZONE if the user has not changed the TIME ZONE of the session. Returns an INTERVAL HOUR TO MINUTE value. (HyperSQL)
SESSION_TIMEZONE
SESSION_TIMEZONE()
Returns the initial (default) time zone displacement for the current session. Returns an INTERVAL HOUR TO MINUTE value. (HyperSQL)
SESSIONTIMEZONE
SESSIONTIMEZONE()
Returns the same value as SESSION_TIMEZONE. (HyperSQL)
DATABASE_TIMEZONE
DATABASE_TIMEZONE()
Returns the time zone displacement for the database engine. This is based on where the database server process is located. Returns an INTERVAL HOUR TO MINUTE value. (HyperSQL)
DBTIMEZONE
DBTIMEZONE()
Similar to DATABASE_TIMEZONE. Returns a string. Works in ORA compatibility mode only.(HyperSQL)
CURRENT_DATE
CURRENT_DATE
CURRENT_TIME
CURRENT_TIME [ ( <time precision> )
]
LOCALTIME
LOCALTIME [ ( <time precision> ) ]
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP [ ( <timestamp precision> )
]
LOCALTIMESTAMP
LOCALTIMESTAMP [ ( <timestamp precision> )
]
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 with the time zone of the SQL session.
LOCALTIME returns a value of TIME type.
CURRENT_TIMESTAMP returns a value of TIMESTAMP WITH
TIME ZONE type with the time zone of the SQL session.
LOCALTIMESTAMP returns a value of TIMESTAMP type. If
the optional [ ( <time precision> ) ]
or [ ( <timestamp precision> ) ] is used, then
the returned value has the specified fraction of the second precision.
When the functions are used multiple times in a single SQL statement,
the returned values represent the same point of time.
From version 2.7.0 the functions return values with microsecond precision. Previous versions returned values with millisecond precision. (SQL:2023)
NOW
NOW ()
This function is equivalent to LOCALTIMESTAMP.
It can be used as a no-arg function as the parens are optional.
(HyperSQL)
CURDATE
CURDATE ()
This function is equivalent to CURRENT_DATE.
(JDBC)
CURTIME
CURTIME ()
This function is equivalent to LOCALTIME.
(JDBC)
SYSDATE
SYSDATE
This no-arg function is similar to LOCALTIMESTAMP
but it returns the timestamp of the HyperSQL server as opposed
to the current session. The returned timestamp is without fraction of
second. (HyperSQL)
SYSTIMESTAMP
SYSTIMESTAMP
This no-arg function is similar to
CURRENT_TIMESTAMP but it returns the timestamp with
time zone of the HyperSQL server as opposed to the current session. The
function is enabled in ORA syntax mode only. (HyperSQL)
TODAY
TODAY
This no-arg function is equivalent to CURRENT_DATE.
(HyperSQL)
DATENAME, DATEPART and EOMONTH
These functions are available in the MSS compatibility mode and
perform the equivalent of EXTRACT function or the
LAST_DAY function. (HyperSQL)
DAYNAME
DAYNAME ( <datetime value expr> )
This function is equivalent to EXTRACT ( DAY_NAME FROM
... ) Returns a string in the range of Sunday - Saturday.
(JDBC)
DAYOFMONTH
DAYOFMONTH ( <datetime value expr>
)
This function is equivalent to EXTRACT ( DAY_OF_MONTH
FROM ... ) Returns an integer value in the range of 1-31.
(JDBC)
DAYOFWEEK
DAYOFWEEK ( <datetime value expr>
)
This function is equivalent to EXTRACT ( DAY_OF_WEEK FROM
... ) Returns an integer value in the range of 1-7. The first
day of the week is Sunday. (JDBC)
DAYOFYEAR
DAYOFYEAR ( <datetime value expr>
)
This function is equivalent to EXTRACT ( DAY_OF_YEAR FROM
... ) Returns an integer value in the range of 1-366.
(JDBC)
DAYS
DAYS ( <datetime value expr> )
The <datetime value expr> is of DATE or
TIMESTAMP type. This function returns the DAY number since the first day
of the calendar. The first day is numbered 1. (HyperSQL)
HOUR
HOUR ( <datetime value expr> )
This function is equivalent to EXTRACT ( HOUR FROM ... )
Returns an integer value in the range of 0-23. (JDBC)
MINUTE
MINUTE ( <datetime value expr> )
This function is equivalent to EXTRACT ( MINUTE FROM ...
) Returns an integer value in the range of 0 - 59.
(JDBC)
MONTH
MONTH ( <datetime value expr> )
This function is equivalent to EXTRACT ( MONTH FROM ... )
Returns an integer value in the range of 1-12. (JDBC)
MONTHNAME
MONTHNAME ( <datetime value expr>
)
This function is equivalent to EXTRACT ( NAME_OF_MONTH
FROM ... ) Returns a string in the range of January -
December. (JDBC)
QUARTER
QUARTER ( <datetime value expr> )
This function is equivalent to EXTRACT ( QUARTER FROM ...
) Returns an integer in the range of 1 - 4. (JDBC)
SECOND
SECOND ( <datetime value expr> )
This function is equivalent to EXTRACT ( SECOND FROM ...
) Returns a decimal in the range of 0 - 60, with the same
precision as the <datetime value expr>. (JDBC)
SECONDS_SINCE_MIDNIGHT
SECONDS_SINCE_MIDNIGHT ( <datetime value expr>
)
This function is equivalent to EXTRACT (
SECONDS_SINCE_MIDNIGHT FROM ... ) Returns an integer in the
range of 0 - 86399. (HyperSQL)
UNIX_MILLIS
UNIX_MILLIS ( [ <datetime value expression> ] )
This function returns a BIGINT value. With no parameter, it returns the number of milliseconds since 1970-01-01. With a DATE or TIMESTAMP parameter, it converts the argument into number of milliseconds since 1970-01-01. (HyperSQL)
UNIX_TIMESTAMP
UNIX_TIMESTAMP ( [ <datetime value expression> ] )
This function returns a BIGINT value. With no parameter, it
returns the number of seconds since 1970-01-01. With a DATE or TIMESTAMP
parameter, it converts the argument into number of seconds since
1970-01-01. See also the TIMESTAMP ( <num value
expression> ) function to return a TIMESTAMP from a Unix
timestamp. (HyperSQL)
WEEK
WEEK ( <datetime value expr> )
This function is equivalent to EXTRACT ( WEEK_OF_YEAR
FROM ... ) Returns an integer in the range of 1 - 54.
(JDBC)
YEAR
YEAR ( <datetime value expr> )
This function is equivalent to EXTRACT ( YEAR FROM ... )
Returns an integer in the range of 1 - 9999. (JDBC)
EXTRACT
EXTRACT ( <extract field> FROM <extract
source> )
<extract field> ::= YEAR | MONTH | DAY | HOUR |
MINUTE | DAY_OF_WEEK | WEEK_OF_YEAR | QUARTER | DAY_OF_YEAR |
DAY_OF_MONTH |
TIMEZONE_HOUR | TIMEZONE_MINUTE | SECOND |
SECONDS_SINCE_MIDNIGHT |
DAY_NAME | MONTH_NAME
<extract source> ::= <datetime value expr> |
<interval value expr>
The EXTRACT function returns a field or element of the
<extract source>. The <extract
source> is a datetime or interval expression. The type of
the return value is BIGINT for most of the <extract
field> options. The exception is SECOND,
where a DECIMAL value is returned which has the same precision as the
datetime or interval expression. The field values DAY_NAME
or MONTH_NAME result in a character string.
When MONTH_NAME is specified, a string in the range
January - December is returned. When DAY_NAME is
specified, a string in the range Sunday -Saturday is returned.
If the <extract source> is FROM
<datetime value expr>, different groups of
<extract source> can be used depending on the
data type of the expression. The TIMEZONE_HOUR |
TIMEZONE_MINUTE options are valid only for TIME WITH TIMEZONE
and TIMESTAMP WITH TIMEZONE data types. The HOUR | MINUTE |
SECOND | SECONDS_MIDNIGHT options, are valid for TIME and
TIMESTAMP types. The rest of the fields are valid for DATE and TIMESTAMP
types.
If the <extract source> is FROM
<interval value expr>, the <extract
field> must be one of the fields of the INTERVAL type of
the expressions. The YEAR | MONTH options may be
valid for INTERVAL types based on months. The DAY | HOUR |
MINUTE | SECOND | SECONDS_MIDNIGHT options may be valid for
INTERVAL types based on seconds. For example, DAY | HOUR |
MINUTE are the only valid fields for the INTERVAL DAY TO
MINUTE data type. (SQL:2023 with HyperSQL extensions)
NEXT_DAY
NEXT_DAY ( <datetime value expr>, <character
value expr>)
This function returns a TIMESTAMP for compatibility reasons. The
return value is the next weekday named by the second argument that
occurs after the first date. For example, next Wednesday is expressed as
NEXT_DAY(CURRENT_DATE, 'WEDNESDAY').
(HyperSQL)
ADD_MONTHS
ADD_MONTHS ( <datetime value expr>, <numeric
value expr>)
This function is similar but different to simple addition of a
MONTH interval to a datetime value. The SQL Standard expression,
<datetime value expr> + n MONTH, when used with
the last day of a short month such as February, returns a date that has
the same day of the month in the target month. The
ADD_MONTHS function adjusts the target day to the
last day of the target month. For all other days, the behaviour is the
same. This function always returns a TIMESTAMP(0) value, regardless of
the type of the argument. (HyperSQL)
The example below compares the output of the function and the expression.
VALUES ADD_MONTHS ( DATE '2012-02-29' , 1), DATE '2012-02-29' + 1 MONTH C1 C2 ------------------- ---------- 2012-03-31 00:00:00 2012-03-29
LAST_DAY
LAST_DAY ( <datetime value expr> )
Returns the last day of the month for the given
<datetime value expr>. The returned value
preserves the year, month, hour, minute and second fields of the
timestamp. The type of the result is always TIMESTAMP(0).
(HyperSQL)
VALUES LAST_DAY ( TIMESTAMP '2012-02-14 12:30:44') C1 ------------------- 2012-02-29 12:30:44
MONTHS_BETWEEN
MONTHS_BETWEEN ( <datetime value expr1> ,
<datetime value expr2> )
Returns a number (not an INTERVAL) possibly with a fraction, representing the number of months between two days. If both dates have the same day of month, or are on the last day of the month, the result is an exact numeric. Otherwise, the fraction is calculated based on 31 days per month. You can cast the resulting value into INTERVAL MONTH and use it for datetime arithmetic. (HyperSQL)
VALUES MONTHS_BETWEEN ( TIMESTAMP '2013-02-14 12:30:44', TIMESTAMP '2012-01-04 12:30:44') C1 ----------------------------------- 13.32258064516129000000000000000000
TIMESTAMPADD
TIMESTAMPADD ( <tsi datetime field>, <numeric
value expression>, <datetime value expr>)
TIMESTAMPDIFF
TIMESTAMPDIFF ( <tsi datetime field>, <datetime
value expr 1>, <datetime value expr 2>)
<tsi datetime field> ::= SQL_TSI_FRAC_SECOND |
SQL_TSI_MILLI_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
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
<datetime value expr> from another in the given
units of date or time using the minus operator. An example of
<datetime value expr> + <numeric value expression>
<datetime field> is LOCALTIMESTAMP + 5
DAY. An example of ( <datetime value expr> -
<numeric value expression> ) <datetime field> is
(CURRENT_DATE - DATE '2008-08-8') MONTH which returns
the number of calendar months between the two dates.
The two JDBC functions, TIMESTAMPADD and
TIMESTAMPDIFF perform a similar function to the above
SQL expressions. The <tsi datetime 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. With TIMESTAMPDIFF, the names indicate the
unit of time used to compute the difference between two datetime fields.
With TIMESTAMPADD they represent the unit of time
used for the <numeric value expression>. The unit of time for each
name is self-explanatory. In the case of SQL_TSI_FRAC_SECOND, the unit
is nanosecond.
The type of the return value for TIMESTAMPADD
is the same as the type of the datetime argument used, except when the
argument type is DATE and a unit smaller than DAY is added the return
type is TIMESTAMP. 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. The TIME type is not supported for the arguments to these
functions.
TIMESTAMPDIFF is evaluated as <datetime
value expr 2> - <datetime value expr 1>. (JDBC)
TIMESTAMPADD ( SQL_TSI_MONTH, 3, DATE '2008-11-22' ) TIMESTAMPDIFF ( SQL_TSI_HOUR, TIMESTAMP '2008-11-20 20:30:40', TIMESTAMP '2008-11-21 21:30:40' )
DATE_ADD
DATE_ADD ( <datetime value expr> , <interval
value expr> )
DATE_SUB
DATE_SUB ( <datetime value expr> , <interval
value expr> )
These functions are equivalent to arithmetic addition and
subtraction, <datetime value expr> + <interval value
expr> and <datetime value expr> -
<interval value expr>. The functions are provided for
compatibility with other databases. The supported interval units are the
standard SQL interval unit listed in other chapters of this guide. The
TIME type is supported for the argument to these functions.
(HyperSQL)
DATE_ADD ( DATE '2008-11-22', INTERVAL 3 MONTH ) DATE_SUB ( TIMESTAMP '2008-11-22 20:30:40', INTERVAL 20 HOUR )
DATEADD
DATEADD ( <field>, <numeric value expr>,
<datetime value expr> )
DATEDIFF
DATEDIFF ( <field>, <datetime value expr 1>,
<datetime value expr 2> )
<field> ::= 'yy' | 'year' | 'qq' | 'quarter' |'mm'
| 'month' | 'dd' | 'day' | 'hh' | 'hour' | 'mi' | 'minute' | 'ss' |
'second' | 'ms' | 'millisecond' | 'mcs' | 'microsecond' | 'ns' |
'nanosecond'
<field> ::= YY | YEAR | QQ | QUARTER | MM | MONTH |
DD | DAY | HH | HOUR | MI | MINUTE | SS | SECOND | MS | MILLISECOND |
MCS | MICROSECOND | NS | NANOSECOND
The DATEADD and DATEDIFF
functions are alternatives to TIMESTAMPADD and
TIMESTAMPDIFF, with similar field options. The field
names are specified as strings or as keywords. The short field names
translate to YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, MILLISECOND,
MICROSECOND and NANOSECOND. DATEDIFF is evaluated as
<datetime value expr 2> - <datetime value expr
1>. (HyperSQL}
DATEDIFF ( <datetime value expr 1>, <datetime
value expr 2> )
This special form of DATEDIFF does not have a
field parameter and return the number of days between two dates. This
form is evaluated as <datetime value expr 1> -
<datetime value expr 2>, which is different from the
main form. This form is compatible with some other database engines. The
TIME type is not supported for the arguments to these functions.
(HyperSQL}
DATEADD ( 'month', 3, DATE '2008-11-22' ) DATEDIFF ( 'hour', TIMESTAMP '2008-11-22 20:30:40', TIMESTAMP '2008-11-22 00:30:40' )
ROUND
ROUND ( <datetime value expr> [ , <char value
expr> ] )
The <datetime value expr> is of DATE,
TIME or TIMESTAMP type. The <char value expr>
is one of the format strings: 'YYYY', 'YY', 'MONTH', 'MM', 'WW', 'IW',
'DDD', 'DD', 'HH24', 'HH12', 'HH', 'MI', 'SS'; represeting YEAR, MONTH,
WEEK OF YEAR, ISO WEEK, DAY, HOUR, MINUTE or SECOND as listed in the
table for TO_CHAR and TO_DATE format elements (see below). The datetime
value is rounded up or down after the specified field and the rest of
the fields to the right are set to the minimum value (one for MONTH and
DAY, or zero, for the rest of the fields). For example, rounding a
timestamp value on the DAY field results in midnight the same date or
midnight the next day if the time is at or after 12 noon. If the second
argument is omitted, the datetime value is rounded to the nearest day.
(HyperSQL)
TRUNC
TRUNC ( <datetime value expr> [ , <char value
expr> ] )
Similar to the ROUND function, the datetime value is truncated
after the specified field and the rest of the fields to the right are
set to the minimum value (one for MONTH and DAY, or zero, for the rest
of the fields). For example, applying TRUNC to a
timestamp value on the DAY field results in midnight the same date.
Examples of ROUND and TRUNC
functions are given below. If the second argument is omitted, the
datetime value is truncated to midnight the same date. (HyperSQL)
ROUND ( TIMESTAMP'2008-08-01 20:30:40', 'YYYY' ) TIMESTAMP '2009-01-01 00:00:00' TRUNC ( TIMESTAMP'2008-08-01 20:30:40', 'YYYY' ) TIMESTAMP '2008-01-01 00:00:00'
FROM_TZ
FROM_TZ ( <timestamp value expr>, <zone or
interval spec string> )
This function takes the date-time (year, month, day, hour, minute, second, fraction) from the first argument and the time zone from the second argument to construct a TIMESTAMP WITH TIME ZONE value. The output can represent a different point of universal time (UTC) than the input. This is different from the AT TIME ZONE expression which keeps the UTC value and changes the zone. (HyperSQL)
-- the output shows the same timestamp as the input, but with the time zone of the specified region VALUES FROM_TZ(TIMESTAMP'2022-03-28 11:00:00+4:00','America/Chicago') C1 ------------------------------- 2022-03-28 11:00:00.000000-5:00 -- this example has the same output as the previous one VALUES FROM_TZ(TIMESTAMP'2022-03-28 11:00:00','-5:00')
NUMTODSINTERVAL
NUMTODSINTERVAL ( <numeric value expr>,
<interval spec string> )
This function converts the numeric value to an interval, exactly like CAST. The interval spec string is one of 'DAY', 'HOUR', 'MINUTE', 'SECOND'. (HyperSQL)
NUMTOYMINTERVAL
NUMTOYMINTERVAL ( <numeric value expr>,
<interval spec string> )
This function converts the numeric value to an interval, exactly like CAST. The interval spec string is 'YEAR' or 'MONTH'. (HyperSQL)
TIMESTAMP
TIMESTAMP ( <num value expr> )
TIMESTAMP ( <char value expr> )
TIMESTAMP ( <char value expr>, <char value
expr> )
TIMESTAMP ( <date value expr>, <time value
expr> )
This function translates the arguments into a TIMESTAMP WITHOUT TIME ZONE value.
When the single argument is a numeric value, it is interpreted as a Unix timestamp in seconds.
When the single argument is a formatted date or timestamp string, it is translated to a TIMESTAMP.
When two arguments are used, the first argument is the DATE part and the second argument is the TIME part of the returned TIMESTAMP value. The types of the arguments can be DATE and TIME respectively, or they can be any character string type. An example, including the result, is given below:
TIMESTAMP ( '2008-11-22', '20:30:40' ) TIMESTAMP '2008-11-22 20:30:40.000000'
TIMESTAMP_WITH_ZONE
TIMESTAMP_WITH_ZONE ( <num value expr> )
TIMESTAMP_WITH_ZONE ( <char value expr> )
This function translates the arguments into a TIMESTAMP WITH TIME ZONE value.
When the single argument is a numeric value, it is interpreted as a Unix timestamp in seconds.
When the single argument is TIMESTAMP, it is converted to TIMESTAMP WITH TIME ZONE.
The time zone of the returned value is the local time zone at the time of the timestamp argument. This accounts for daylight saving times. For example, if the local time zone was +4:00 at the time of the given Unix timestamp, the returned value is local timestamp at the time with time zone +4:00.
TO_CHAR
TO_CHAR ( <datetime value expr>, <char value
expr> )
This function formats a datetime value to the format given in the second argument. See TO_TIMESTAMP below for the details of the format string. Format elements such as DAY or HH12 are used in the examples below, with the result included.
Separator characters (space, comma, period, hyphen, colon, semicolon, forward slash) can be included between the pattern elements. You can include a string literal inside the format string by enclosing it in double quotes (see the second example above). Unsupported format characters should not be used. (HyperSQL)
TO_CHAR ( TIMESTAMP'2008-02-01 20:30:40', 'YYYY BC DAY DD MONTH HH12 AM' ) 2008 AD Friday 01 February 08 pm TO_CHAR ( DATE'2018-03-01', '"in" YYYY", on" DD MONTH' ) in 2018, on 01 March
TO_DATE
TO_DATE ( <char value expr>, <char value
expr> )
TO_TIMESTAMP
TO_TIMESTAMP ( <char value expr>, <char value
expr> )
TO_TIMESTAMP_TZ
TO_TIMESTAMP_TZ ( <char value expr>, <char value
expr> )
These functions translate a formatted datetime string to a TIMESTAMP. TO_DATE returns a TIMESTAMP(0) value with no sub-second fraction. TO_TIMESTAMP returns a TIMESTAMP(6). TO_TIMESTAMP_TZ returns a TIMESTAMP(6) WITH TIME ZONE value. The format string can contain pattern elements from the list given below, plus punctuation and space characters. The pattern should contain all the necessary fields to construct a date, including, year, month, day of month, etc. The returned timestamp can then be cast into DATE if necessary. Two examples together with the result are given below:
TO_TIMESTAMP ( '22/11/2008 20:30:40', 'DD/MM/YYYY HH:MI:SS' ) TO_TIMESTAMP ( '22/11/2008 20:30:40', 'JAVA:dd/MM/uuuu HH:mm:ss' ) // Java format TIMESTAMP '2008-11-22 20:30:40.000000'
The format strings that can be used for TO_DATE and TO_TIMESTAMP are more restrictive than those used for TO_CHAR, because the format string must contain the elements needed to build a full DATE or TIMESTAMP value. For example, you cannot use the 'W', 'HH12' format elements.
The format is internally translated to a
java.time.format.DateTimeFormatter format string.
Unsupported format strings should not be used. With TO_CHAR, you can
include a string literal inside the format string by enclosing it in
double quotes.
From version 2.7.3 Java format strings as defined by
java.time.format.DateTimeFormatter can be used
directly as an alternative. These format strings must have the
'JAVA:' prefix as in the second example above, and
the literals inside the format string must be enclosed in double single
quotes as required by Java
DateTimeFormatter.(HyperSQL)
The supported format components are all uppercase as follows:
Table 4.2. TO_CHAR, TO_DATE and TO_TIMESTAMP format elements
BC | B.C. | AD | A.D. | Returns AD for common era. |
RRRR |
4-digit year |
YYYY |
4-digit year |
IYYY |
4-digit year, corresponding to ISO week of the year. The reported year for the last few days of the calendar year may be the next year. |
YY |
2 digit year |
IY |
2 digit year, corresponding to ISO week of the year |
MM |
Month (01-12) |
MON |
Short three-letter name of month |
MONTH |
Name of month |
IW |
Week of year (1-52 or 1-53) based on the ISO standard. Week starts on Monday. The first week may start near the end of previous year. |
W |
Week of month (0-5) where the first day of the month is in week 1, or in week 0 for an ISO week at the beginning of the calendar year which starts in the previous week-based year. Week starts on Monday. |
DAY |
Name of day. |
DD |
Day of month (01-31). |
DDD |
Day of year (1-366). |
DY |
Short three-letter name of day. |
HH12 |
Hour of day (0-11). |
AM | A.M. | PM | P.M. |
Meridian indicator. |
HH |
Hour of day (00-23). |
HH24 |
Hour of day (00-23). |
MI |
Minute (00-59). |
SS |
Second (00-59). |
FF[1-9] |
Fractional seconds. With TO_CHAR, 'FF' indicates six digits are written. 'FF' followed by a single digit writes the indicated number of digits. For example 'FF2' means 2 digits after the fraction. With TO_TIMESTAMP, 'FF' indicates a variable number of up to 9 digits are read. |
TZ |
Time zone hours and minutes. |
Array functions are specialised functions with ARRAY parameters or return values. For the ARRAY_AGG aggregate function, see the Data Access and Change chapter.
CARDINALITY
CARDINALITY( <array value expr> )
Returns the element count for the given array argument. (SQL:2023)
MAX_CARDINALITY
MAX_CARDINALITY( <array value expr>
)
Returns the maximum allowed element count for the given array argument. (SQL:2023)
POSITION_ARRAY
POSITION_ARRAY( <value expression> IN <array value
expr> [ FROM <int value expr> ] )
Returns the position of the first match for the <value
expression> in the array. By default, the search starts from
the beginning of the array. The optional <int value
expr> specifies the start position. Positions are counted
from 1. Returns zero if no match is found. (HyperSQL)
SORT_ARRAY
SORT_ARRAY( <array value expr> [ { ASC | DESC } ] [
NULLS { FIRST | LAST } ] )
Returns a sorted copy of the array. By default, sort is performed in ascending order and NULL elements are sorted first. (HyperSQL)
TRIM_ARRAY
TRIM_ARRAY( <array value expr>, <num value
expr> )
Returns a new array that contains the elements of the
<array value expr> minus the number of elements
specified by the <num value expr>. Elements are
discarded from the end of the array. (SQL:2023)
SEQUENCE_ARRAY
SEQUENCE_ARRAY( <value expr 1>, <value expr 2>,
<value expr 3 )
Returns a new array that contains a sequence of values. The
<value expr 1> is the lower bound of the range.
The <value expr 2> is the upper bound of the
range. The <value expr 3> is the increment. The
elements of the array are within the inclusive range. The first element is
<value expr 1> and each subsequent element is the
sum of the previous element and the increment. If the increment is zero,
only the first element is returned. When the increment is negative, the
lower bound should be larger than the upper bound. The type of the
arguments can be all number types, or a datetime range and an interval for
the third argument (HyperSQL)
In the examples below, a number sequence and a date sequence are shown. The UNNEST table expression is used to form a table from the array.
SEQUENCE_ARRAY(0, 100, 5) ARRAY[0,5,10,15,20,25,30,35,40,45,50,55,60,65,70,75,80,85,90,95,100] SELECT * FROM UNNEST(SEQUENCE_ARRAY(10, 12, 1)) C1 -- 10 11 12 SELECT * FROM UNNEST(SEQUENCE_ARRAY(CURRENT_DATE, CURRENT_DATE + 6 DAY, 1 DAY)) WITH ORDINALITY AS T(D, I) D I ---------- - 2010-08-01 1 2010-08-02 2 2010-08-03 3 2010-08-04 4 2010-08-05 5 2010-08-06 6 2010-08-07 7
General functions can take different types of arguments. Some General Functions accept a variable number of arguments.
Also see the Data Access and Change chapter for SQL expressions that are
similar to functions, for example CAST and
NULLIF.
CASEWHEN
CASEWHEN( <boolean value expr>, <value expr 2>,
<value expr 3> )
If the <boolean value expr> is true,
returns <value expr 2> otherwise returns
<value expr 3>. Use a CASE WHEN expression instead for more
extensive capabilities and options. CASE WHEN
is documented in the Data Access and Change chapter. (HyperSQL)
COALESCE
COALESCE( <value expr 1>, <value expr 2> [,
...] )
Returns <value expr 1> if it is not null,
otherwise returns <value expr 2> if not null and
so on. The type of both arguments must be comparable. (SQL:2023)
CONVERT
CONVERT ( <value expr> , <data type>
)
<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> ) ]
The CONVERT function is a JDBC escape function, equivalent to the
SQL standard CAST expression. It converts the <value
expr> into the given <data type> and
returns the value. The <data type> options are
synthetic names made by prefixing type names with SQL_.
Some of the <data type> options represent valid
SQL types, but some are based on non-standard type names, namely
{ SQL_LONGNVARCHAR | SQL_LONGVARBINARY |SQL_LONGVARCHAR |
SQL_TINYINT }. None of the synthetic names can be used in any
other context than the CONVERT function.
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. Defaults are used for precision.
HyperSQL also allows the use of real type names (without the
SQL_ prefix). In this usage, HyperSQL allows the use of
precision, scale or length for the type definition when they are valid for
the type definition.
When MS SQL Server compatibility mode is on, the parameters of CONVERT are switched and only the real type names with required precision, scale or length are allowed. (JDBC)
DECODE
DECODE( <value expr main>, <value expr match
1>, <value expr result 1> [...,] [, <value expr default>]
)
DECODE takes at least 3 arguments. The <value expr
main> is compared with <value expr match
1> and if it matches, <value expr result
1> is returned. If there are additional pairs of
<value expr match n> and <value expr
result n>, comparison is repeated until a match is found the
result is returned. If no match is found, the <value expr
default> is returned if it is specified, otherwise NULL is
returned. The type of the return value is a combination of the types of
the <value expr result ... > arguments.
(HyperSQL)
GREATEST
GREATEST( <value expr 1>, [<value expr ...>,
...] )
The GREATEST function takes one or more arguments. It compares the arguments with each other and returns the greatest argument. Arguments can be of any type, so long as they are comparable. The return type is the combined type of the arguments. For example comparing an integer value with a double value will return the greatest value as a DOUBLE. (SQL:2023)
IFNULL
ISNULL
IFNULL | ISNULL ( <value expr 1>, <value expr
2> )
Returns <value expr 1> if it is not null,
otherwise returns <value expr 2>. The type of the
return value is the type of <value expr 1>.
Almost equivalent to SQL Standard COALESCE(<value expr 1>,
<value expr 2>) function, but without type aggregation.
(JDBC)
LEAST
LEAST( <value expr 1>, [<value expr ...>, ...]
)
The LEAST function takes one or more arguments. It compares the arguments with each other and returns the smallest argument. Arguments can be of any type, so long as they are comparable. The return type is the combined type of the arguments. For example comparing an integer value with a double value will return the smallest value as a DOUBLE. (SQL:2023)
LOAD_FILE
LOAD_FILE ( <char value expr 1> [, <char value
expr 2>] )
Returns a BLOB or CLOB containing the URL or file path specified in the first argument. If used with a single argument, the function returns a BLOB. If used with two arguments, the function returns a CLOB and the second argument is the character encoding of the file.
The file path is interpreted the same way as a TEXT TABLE source
file location. The hsqldb.allow_full_path system
property must be set true in order to access files
outside the directory structure of the database files.
(HyperSQL)
NULLIF
NULLIF( <value expr 1>, <value expr 2>
)
Returns <value expr 1> if it is not equal
to <value expr 2>, otherwise returns null. The
type of both arguments must be the same. This function is a shorthand for
a specific CASE expression. (SQL:2023)
NVL
NVL( <value expr 1>, <value expr 2>
)
Returns <value expr 1> if it is not null,
otherwise returns <value expr 2>. The type of the
return value is the type of <value expr 1>. For
example, if <value expr 1> is an INTEGER column
and <value expr 2> is a DOUBLE constant, the
return type is cast into INTEGER. This function is similar to IFNULL.
(HyperSQL)
NVL2
NVL2( <value expr 1>, <value expr 2>, <value
expr 3> )
If <value expr 1> is not null, returns
<value expr 2>, otherwise returns
<value expr 3>. The type of the return value is
the type of <value expr 2> unless it is null.
(HyperSQL)
UUID
UUID ( [ { <char value expr> | <binary value
expr> } ] )
With no parameter, this function returns a new UUID value as a 16-byte binary value in the UUID type. With a UUID hexadecimal string argument, it returns the 16-byte binary value in UUID. With a 16-byte binary or UUID argument, it returns the formatted UUID character representation. Note UUID is a type derived from BINARY(16) that in represented as a hexadecimal character string with the required hyphens. (HyperSQL)
NEWID
NEWID ()
This is a synonym for the no-arg UUID function in MSS compatibility mode. (HyperSQL)
SYS_GUID
SYS_GUID ()
Returns a UUID value as a 16 byte binary value in ORA compatibility mode. (HyperSQL)
CRYPT_KEY
CRYPT_KEY( <value expr 1>, <value expr 2>
)
Returns a binary string representation of a random cryptography key
for the given cipher and cryptography provider. The cipher specification
is specified by <value expr 1> and the provider
by <value expr 2>. To use the default provider,
specify null for <value expr 2>.
(HyperSQL)
DIAGNOSTICS
DIAGNOSTICS ( ROW_COUNT )
This is a convenience function for use instead of the GET
DIAGNOSTICS ... statement. The argument specifies the name of
the diagnostics variable. Currently the only supported variable is the
ROW_COUNT variable. The function returns the row count
returned by the last executed statement. The return value is 0 after most
statements. Calling this function immediately after executing an INSERT,
UPDATE, DELETE or MERGE statement returns the row count for the last
statement, as it is returned by the JDBC statement. (HyperSQL)
IDENTITY
IDENTITY ()
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)
DATABASE
DATABASE ()
Returns the file name (without directory information) of the database. (JDBC)
DATABASE_NAME
DATABASE_NAME ()
Returns the database name. This name is a 16-character, uppercase string. It is generated as a string based on the timestamp of the creation of the database, for example HSQLDB32438AEAFB. The name can be redefined by an admin user but the new name must be all uppercase and 16 characters long. This name is used in log messages with external logging frameworks. (HyperSQL)
DATABASE_VERSION
DATABASE_VERSION ()
Returns the full version string for the database engine. For example, 2.7.4. (JDBC)
USER
USER ()
Equivalent to the SQL function CURRENT_USER.
(JDBC)
CURRENT_USER
CURRENT_USER
CURRENT_ROLE
CURRENT_ROLE
SESSION_USER
SESSION_USER
SYSTEM_USER
SYSTEM_USER
CURRENT_SCHEMA
CURRENT_SCHEMA
CURRENT_CATALOG
CURRENT_CATALOG
These functions return the named current session attribute. They are all SQL Standard functions.
The CURRENT_USER is the user that connected to
the database, or a user subsequently set by the SET
AUTHORIZATION statement.
SESSION_USER is the same as CURRENT_USER
SYSTEM_USER is the user that connected to the
database. It is not changed with any command until the session is
closed.
CURRENT_SCHEMA is default schema of the user, or
a schema subsequently set by the SET SCHEMA
command.
CURRENT_CATALOG is always the same within a given
HyperSQL database and indicates the name of the catalog.
IS_AUTOCOMMIT
IS_AUTOCOMMIT()
Returns TRUE if the session is in auto-commit mode. (HyperSQL)
IS_READONLY_SESSION
IS_READONLY_SESSION()
Returns TRUE if the session is in read only mode. (HyperSQL)
IS_READONLY_DATABASE
IS_READONLY_DATABASE()
Returns TRUE if the database is a read only database. (HyperSQL)
IS_READONLY_DATABASE_FILES
IS_READONLY_DATABASE_FILES()
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)
ISOLATION_LEVEL
ISOLATION_LEVEL()
Returns the current transaction isolation level for the session. Returns either READ COMMITTED or SERIALIZABLE as a string. (HyperSQL)
SESSION_ID
SESSION_ID()
Returns the id of the session as a BIGINT value. Each session id is unique during the operational lifetime of the database. Id's are restarted after a shutdown and restart. (HyperSQL)
SESSION_ISOLATION_LEVEL
SESSION_ISOLATION_LEVEL()
Returns the default transaction isolation level for the current session. Returns either READ COMMITTED or SERIALIZABLE as a string. (HyperSQL)
DATABASE_ISOLATION_LEVEL
DATABASE_ISOLATION_LEVEL()
Returns the default transaction isolation level for the database. Returns either READ COMMITTED or SERIALIZABLE as a string. (HyperSQL)
TRANSACTION_SIZE
TRANSACTION_SIZE()
Returns the row change count for the current transaction. Each row change represents a row INSERT or a row DELETE operation. There will be a pair of row change operations for each row that is updated.
TRANSACTION_ID
TRANSACTION_ID()
Returns the current transaction ID for the session as a BIGINT value. The database maintains a global incremental id which is allocated to new transactions and new actions (statement executions) in different sessions. This value is unique to the current transaction. (HyperSQL)
TRANSACTION_UTC
TRANSACTION_UTC()
Returns the transaction timestamp in UTC time zone for the session. This timestamp is used in updates made to system-versioned tables during the transaction. (HyperSQL)
ACTION_ID
ACTION_ID()
Returns the current action ID for the session as a BIGINT value. The database maintains a global incremental id which is allocated to new transactions and new actions (statement executions) in different sessions. This value is unique to the current action. (HyperSQL)
TRANSACTION_CONTROL
TRANSACTION_CONTROL()
Returns the current transaction model for the database. Returns LOCKS, MVLOCKS or MVCC as a string. (HyperSQL)
LOB_ID
LOB_ID( <column reference> )
Returns internal ID of a lob as a BIGINT value. Lob ID's are unique and never reused. The <column reference> is the name of the column (or variable, or argument) which is a CLOB or BLOB. Returns null if the value is null. (HyperSQL)
ROWNUM
ROWNUM()
ROW_NUMBER
ROW_NUMBER() OVER()
Returns the current row number (from 1) being processed in a select
statement. This has the same semantics as the ROWNUM pseudo-column in ORA
syntax mode, but can be used in any syntax mode. The function is used in a
SELECT or DELETE statement. The ROWNUM of a row is incremented as the rows
are added to the result set. It is therefore possible to use a condition
such as WHERE ROWNUM() < 10, but not ROWNUM() > 10 or ROWNUM = 10.
The ROW_NUMBER() OVER() alternative performs the same
function and is included for compatibility with other database
engines.(HyperSQL)
$Revision: 6787 $
| | |
|
| Chapter 3. Schemas and Database Objects | | Chapter 5. Data Access and Change |