$Revision: 3601 $
$Date: 20100531 20:17:47 0400 (Mon, 31 May 2010) $
Table of Contents
HyperSQL supports a wide range of builtin functions and allows userdefined functions written in SQL and Java languages. User defined functions are covered in a separate chapter. If a builtin function is not available, you can write your own using SQL. Aggregate functions are discussed in chapters that cover SQL in general.
The builtin functions fall into three groups:
SQL Standard Functions
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 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 BuiltIn Functions
Several additional builtin 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,
unless the function is in the SQL/Foundation part of the SQL
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)
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)
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. Equivalent to the SQL
concatenation expression <value expr 1>  <value expr
2>
. (JDBC)
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 04 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. Inbetween values are returned for partial similarity. (JDBC)
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)
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>, <length> )
Returns a character string consisting of the first
<length>
characters of <char value
expr>
. Equivalent to SQL/Foundation
SUBSTRING(<char value expr> FROM 0 FOR <length>)
.
(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. Equivalent to SQL/Foundation
POSITION(<char value expr 1> IN <char value expr
2>)
. (JDBC)
LTRIM
LTRIM ( <char value expr> )
Returns a character string based on <char value
expr>
with the leading space characters removed. Equivalent
to SQL/Foundation TRIM( LEADING ' ' FROM <char value expr>
)
. (JDBC)
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_MATCHES
REGEXP_MATCHES ( <char value expr>, <regular
expression> )
Returns true if the <char value expr> matches the <regular expression>. The <regular expression> is defined according to Java language rules. (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>
. (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)
RTRIM
RTRIM ( <char value expr> )
Returns a character string based on <char value
expr>
with the trailing space characters removed. Equivalent
to SQL/Foundation TRIM(TRAILING ' ' FROM <character
string>)
. (JDBC)
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. (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)
UCASE
UCASE ( <char value expr> )
Returns a character string that is the lower case version of the
<char value expr>
. Equivalent to SQL/Foundation
UPPER( <char value expr> )
. (JDBC)
CHARACTER_LENGTH
{ CHAR_LENGTH  CHARACTER_LENGTH } ( <char value
expression> [ USING { CHARACTERS  OCTETS } ] )
OCTET_LENGTH
OCTET_LENGTH ( <string value expression>
)
BIT_LENGTH
BIT_LENGTH ( <string value expression>
)
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. (Foundation)
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. (Foundation)
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.
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. (Foundation)
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. (Foundation)
ABS
ABS ( <num value expr>  <interval value expr>
)
Returns the absolute value of the argument as a value of the same type. (JDBC and Foundation)
ACOS
ACOS ( <num value expr> )
Returns the arccosine of the argument in radians as a value of DOUBLE type. (JDBC)
ASIN
ASIN ( <num value expr> )
Returns the arcsine of the argument in radians as a value of DOUBLE type. (JDBC)
ATAN
ATAN ( <num value expr> )
Returns the arctangent of the argument in radians as a value of DOUBLE type. (JDBC)
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. (JDBC and Foundation)
BITAND
BITAND ( <num value expr 1>, <num value expr 2>
)
BITAND ( <bit value expr 1>, <bit value expr 2>
)
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 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)
COS
COS ( <num value expr> )
Returns the cosine of the argument (an angle expressed in radians) as a value of DOUBLE type. (JDBC)
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. (JDBC)
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. (JDBC and Foundation)
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. (JDBC and Foundation)
LN
LN ( <num value expr> )
Returns the natural logarithm of the argument, as a value of DOUBLE type. (Foundation)
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. (JDBC)
MOD ( <num value expr 1>, <num value expr 2> )
MOD
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.
(JDBC and Foundation)
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. (JDBC and Foundation)
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.
The function returns a DOUBLE 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. (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. (JDBC)
SQRT
SQRT ( <num value expr> )
Returns the square root of the argument as a value of DOUBLE type. (JDBC and Foundation)
TAN
TAN ( <num value expr> )
Returns the tangent of the argument (an angle expressed in radians) as a value of DOUBLE type. (JDBC)
TRUNCATE
TRUNCATE ( <num value expr>, <int value expr> )
Returns a value in the same type as <num value
expr>
. 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.0000, 12345.0000, 12345.6700, 12345.6789).
(JDBC)
TIMEZONE
TIMEZONE()
Returns the current time zone for the session. Returns an INTERVAL HOUR TO MINUTE value. (HyperSQL)
SESSION_TIMEZONE
SESSION_TIMEZONE()
Returns the default time zone for the current session. Returns an INTERVAL HOUR TO MINUTE value. (HyperSQL)
DATABASE_TIMEZONE
DATABASE_TIMEZONE()
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)
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> ::= <datatime 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 exceptions 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
<datatime 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. (Foundation
with HyperSQL extensions)
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. 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 [ ( <time precision> ) ]
or [ ( <timestamp precision> ) ]
is used, then
the returned value has the specified fraction of the second precision.
(Foundation)
CURDATE
CURDATE ()
This function is equivalent to CURRENT_DATE.
(JDBC)
CURTIME
CURTIME ()
This function is equivalent to LOCALTIME
.
(JDBC)
DAYNAME
DAYNAME ( <datatime 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 131.
(JDBC)
DAYOFWEEK
DAYOFWEEK ( <datetime value expr> )
This function is equivalent to EXTRACT ( DAY_OF_WEEK FROM
... )
Returns an integer value in the range of 17. 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 1366.
(JDBC)
HOUR
HOUR ( <datetime value expr> )
This function is equivalent to EXTRACT ( HOUR FROM ... )
Returns an integer value in the range of 023. (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 112. (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)
NOW
NOW ()
This function is equivalent to
LOCAL_TIMESTAMP.
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 an integer or decimal in the range of 0  59, 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)
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)
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_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 days using the minus operator. An example of
<datetime value expr> + <numeric value expression>
<datetime field>
is LOCAL_TIMESTAMP + 5
DAY
. An example of ( <datetime value expr> 
<numeric value expression> ) <datetime field>
is
(CURRENT_DATE  DATE '2008088') MONTH
which returns
the number of calendar months between the two dates.
The two JDBC functions, TIMESTAMPADD
and
TIMESTAMPDIFF
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)
DATEADD
DATEADD ( <field>, <numeric value expr>,
<datetime value expr> )
DATEDIFF
DATEDIFF ( <field>, <datetime value expr 1>,
datetime value expr 2> )
<field> ::= 'yy'  'mm'  'dd'  'hh'  'mi'  'ss' 
'ms'
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}
TO_CHAR
TO_CHAR( <datetime value expr>, <char value
expr> )
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:
TO_CHAR ( TIMESTAMP'20080201 20:30:40', 'YYYY BC MONTH, DAY HH') 2008 AD February, Friday 8
The format is internally translated to a
java.text.SimpleDateFormat
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:
Table 10.1. TO CHAR Values
BC  B.C.  AD  A.D.  Returns AD for common era and
BC for before common era 
RRRR 
4digit year 
YYYY 
4digit year 
IYYY 
4digit year 
YY 
2 digit year 
IY 
2 digit year 
IYYY 
4digit year 
MM 
Month (0112) 
MON 
Short threeletter name of month 
MONTH 
Name of month 
WW 
Week of year (153) where week 1 starts on the first day of the year and continues to the seventh day of the year. 
W 
Week of month (15) where week 1 starts on the first day of the month and ends on the seventh. 
IW 
Week of year (152 or 153) based on the ISO standard. 
DAY 
Name of day. 
DD 
Day of month (131). 
DDD 
Day of year (1366). 
DY 
Short threeletter name of day. 
HH 
Hour of day (011). 
HH12 
Hour of day (011). 
HH24 
Hour of day (023). 
MI 
Minute (059). 
SS 
Second (059). 
FF 
Fractional seconds. 
Array functions are specialised functions with ARRAY parameters.
CARDINALITY
CARDINALITY( <array value expr> )
Returns the element count for the given array argument. (Foundation)
MAX_CARDINALITY
MAX_CARDINALITY( <array value expr>
)
Returns the maximum allowed element count for the given array argument. (Foundation)
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. (Foundation)
General functions can take different types of arguments. Some General Functions accept a variable number of arguments.
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. (Foundation)
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 nonstandard 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. 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 SQL_
prefix). (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. The return type is the combined type of the arguments. Arguments can be of any type, so long as they are comparable. (HyperSQL)
IFNULL
IFNULL( <value expr 1>, <value expr 2>
)
Returns <value expr 1>
if it is not null,
otherwise returns <value expr 2>
. The type of
both arguments must be the same. Equivalent to SQL Standard
COALESCE(<value expr 1>, <value expr 2>)
function. (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. The return type is the combined type of the arguments. Arguments can be of any type, so long as they are comparable. (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. (Foundation)
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 combined type of the two value expressions. For
example, if <value expr 1> is an INTEGER column and
<value expr 2>
is a DOUBLE constant, the return
type is DOUBLE. This function is the same as IFNULL and COALESCE
(HyperSQL)
CRYPT_KEY
CRYPT_KEY( <value expr 1>, <value expr 2>
)
Returns a binary string representation of a cryptography key for the
given cipher and cyptography 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)
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_VERSION
DATABASE_VERSION ()
Returns the full version string for the database engine. For example, 2.0.1. (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.
ISAUTOCOMMIT
ISAUTOCOMMIT()
Returns TRUE if the session is in autocommit mode. (HyperSQL)
ISREADONLYSESSION
ISREADONLYSESSION()
Returns TRUE if the session is in read only mode. (HyperSQL)
ISREADONLYDATABASE
ISREADONLYDATABASE()
Returns TRUE if the database is a read only database. (HyperSQL)
ISREADONLYDATABASEFILES
ISREADONLYDATABASEFILES()
Returns TRUE if the database is a readonly 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_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_CONTROL
TRANSACTION_CONTROL()
Returns the current transaction model for the database. Returns LOCKS, MVLOCKS or MVCC as a string. (HyperSQL)
$Revision: 3601 $