Chapter 10. Built In Functions

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 are discussed in chapters that cover SQL in general.

The built-in functions fall into three groups:

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/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)

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. (Foundation)

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. (Foundation)

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> [ || ...] . (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 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)

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>, <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. Equivalent to SQL/Foundation POSITION(<char value expr 1> IN <char value expr 2>). Without the third argument, LOCATE is equivalent to the SQL Standard POSITION function. (JDBC)

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. (HyperSQL)

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)

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. (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. (Foundation)

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> as a whole. The <regular expression> is defined according to Java language regular expression rules. (HyperSQL)

REGEXP_SUBSTRING

REGEXP_SUBSTRING ( <char value expr>, <regular expression> )

Returns 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 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. (HyperSQL)

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)

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)

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> . (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 arc-cosine of the argument in radians as a value of DOUBLE type. (JDBC)

ASIN

ASIN ( <num value expr> )

Returns the arc-sine of the argument in radians as a value of DOUBLE type. (JDBC)

ATAN

ATAN ( <num value expr> )

Returns the arc-tangent 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> )

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 bit operations on two values, or in the case of BITNOT on a single 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. 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. (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 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. (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)

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. (Foundation)

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.

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 '2008-11-22', INTERVAL 3 MONTH )

DATE_SUB ( TIMESTAMP '2008-11-22 20:30:40', INTERVAL 20 HOUR )

DATEADD ( 'month', 3, DATE '2008-11-22' )

DATEDIFF ( 'hour', TIMESTAMP '2008-11-22 20:30:40', TIMESTAMP '2008-11-22 00:30:40' )

ROUND ( TIMESTAMP'2008-08-01 20:30:40', 'YYYY' )

'2009-01-01 00:00:00'

TRUNC ( TIMESTAMP'2008-08-01 20:30:40', 'YYYY' )

'2008-01-01 00:00:00'

TIMESTAMP ( '2008-11-22', '20:30:40' )

TIMESTAMP '2008-11-22 20:30:40.000000'

TO_CHAR ( TIMESTAMP'2008-02-01 20:30:40', 'YYYY BC MONTH, DAY HH' )

2008 AD February, Friday 8

TO_CHAR ( TIMESTAMP'2008-02-01 20:30:40', '"The Date is" YYYY BC MONTH, DAY HH' )

The Date is 2008 AD February, Friday 8

TO_TIMESTAMP ( '22/11/2008 20:30:40', 'DD/MM/YYYY HH:MI:SS' )

TIMESTAMP '2008-11-22 20:30:40.000000'

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. (Foundation)

MAX_CARDINALITY

MAX_CARDINALITY( <array value expr> )

Returns the maximum allowed element count for the given array argument. (Foundation)

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. (Foundation)

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 elments 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. (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 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. The return type is the combined type of the arguments. Arguments can be of any type, so long as they are comparable. (HyperSQL)

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 modification. (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)

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. (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 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. With a UUID hexadecimal string argument, it returns the 16 byte binary value of the UUID. With a 16 byte binary argument, it returns the formatted UUID character representation. (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 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.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.

IS_AUTOCOMMIT

IS_AUTOCOMMIT()

Returns TRUE if the session is in autocommit 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)

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 Oracle syntax mode, but can be used in any syntax mode. The function is used in a SELECT of 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)