HyperSQL supports a wide range of built-in functions and allows user-defined functions written in SQL and Java languages. User defined functions are covered in a separate chapter. If a built-in function is not available, you can write your own using SQL. Aggregate functions are discussed in chapters that cover SQL in general.

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

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

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)

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 '2008-08-8') 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'2008-02-01 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:

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 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. 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 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_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)