▲
Search:
| Highlight keyword(s)
Home Download Cheat Sheet Documentation Quickstart Installation Tutorial Features Security Performance Advanced Reference Commands Functions • Aggregate • Window Data Types SQL Grammar System Tables Javadoc PDF (2 MB) Support FAQ Error Analyzer Google Group Appendix History License Build Links MVStore Architecture Migration to 2.0 |  | # Functions ## Index ### Numeric Functions
ABS ACOS ASIN ATAN COS COSH COT SIN SINH TAN TANH ATAN2 BITAND BITOR BITXOR BITNOT BITNAND BITNOR | BITXNOR BITGET BITCOUNT LSHIFT RSHIFT ULSHIFT URSHIFT ROTATELEFT ROTATERIGHT MOD CEIL DEGREES EXP FLOOR LN LOG LOG10 ORA_HASH | RADIANS SQRT PI POWER RAND RANDOM_UUID ROUND SECURE_RAND SIGN ENCRYPT DECRYPT HASH TRUNC COMPRESS EXPAND ZERO
### String Functions
ASCII BIT_LENGTH CHAR_LENGTH OCTET_LENGTH CHAR CONCAT CONCAT_WS DIFFERENCE HEXTORAW RAWTOHEX INSERT Function LOWER UPPER LEFT RIGHT | LOCATE LPAD RPAD LTRIM RTRIM BTRIM TRIM REGEXP_REPLACE REGEXP_LIKE REGEXP_SUBSTR REPEAT REPLACE SOUNDEX SPACE STRINGDECODE | STRINGENCODE STRINGTOUTF8 SUBSTRING UTF8TOSTRING QUOTE_IDENT XMLATTR XMLNODE XMLCOMMENT XMLCDATA XMLSTARTDOC XMLTEXT TO_CHAR TRANSLATE
### Time and Date Functions
CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP LOCALTIME LOCALTIMESTAMP DATEADD DATEDIFF DATE_TRUNC LAST_DAY | DAYNAME DAY_OF_MONTH DAY_OF_WEEK ISO_DAY_OF_WEEK DAY_OF_YEAR EXTRACT FORMATDATETIME HOUR MINUTE | MONTH MONTHNAME PARSEDATETIME QUARTER SECOND WEEK ISO_WEEK YEAR ISO_YEAR
### System Functions
ABORT_SESSION ARRAY_GET CARDINALITY ARRAY_CONTAINS ARRAY_CAT ARRAY_APPEND ARRAY_MAX_CARDINALITY TRIM_ARRAY ARRAY_SLICE AUTOCOMMIT CANCEL_SESSION CASEWHEN Function COALESCE CONVERT CURRVAL CSVWRITE | CURRENT_SCHEMA CURRENT_CATALOG DATABASE_PATH DATA_TYPE_SQL DB_OBJECT_ID DB_OBJECT_SQL DECODE DISK_SPACE_USED SIGNAL ESTIMATED_ENVELOPE FILE_READ FILE_WRITE GREATEST LEAST LOCK_MODE LOCK_TIMEOUT | MEMORY_FREE MEMORY_USED NEXTVAL NULLIF NVL2 READONLY ROWNUM SESSION_ID SET TRANSACTION_ID TRUNCATE_VALUE CURRENT_PATH CURRENT_ROLE CURRENT_USER H2VERSION
### JSON Functions
JSON_OBJECT | JSON_ARRAY |
### Table Functions
CSVREAD LINK_SCHEMA | TABLE UNNEST |
## Details Click on the header of the function to switch between railroad diagram and BNF. Non-standard syntax is marked in green. Compatibility-only non-standard syntax is marked in red, don't use it unless you need it for compatibility with other databases or old versions of H2. ## Numeric Functions ### ABS
    ABS( { numeric | interval } )
`ABS (` |
| `numeric` |
| `interval` |
| `)`
Returns the absolute value of a specified value. The returned value is of the same data type as the parameter. Note that `TINYINT, SMALLINT, INT`, and `BIGINT` data types cannot represent absolute values of their minimum negative values, because they have more negative values than positive. For example, for `INT` data type allowed values are from -2147483648 to 2147483647. `ABS`(-2147483648) should be 2147483648, but this value is not allowed for this data type. It leads to an exception. To avoid it cast argument of this function to a higher data type. Example: ABS(I) ABS(CAST(I AS BIGINT)) ### ACOS
    ACOS(numeric)
`ACOS ( numeric )`
Calculate the arc cosine. See also Java `Math.acos`. This method returns a double. Example: ACOS(D) ### ASIN
    ASIN(numeric)
`ASIN ( numeric )`
Calculate the arc sine. See also Java `Math.asin`. This method returns a double. Example: ASIN(D) ### ATAN
    ATAN(numeric)
`ATAN ( numeric )`
Calculate the arc tangent. See also Java `Math.atan`. This method returns a double. Example: ATAN(D) ### COS
    COS(numeric)
`COS ( numeric )`
Calculate the trigonometric cosine. See also Java `Math.cos`. This method returns a double. Example: COS(ANGLE) ### COSH
    COSH(numeric)
`COSH ( numeric )`
Calculate the hyperbolic cosine. See also Java `Math.cosh`. This method returns a double. Example: COSH(X) ### COT
    COT(numeric)
`COT ( numeric )`
Calculate the trigonometric cotangent (`1/TAN(ANGLE)`). See also Java `Math.*` functions. This method returns a double. Example: COT(ANGLE) ### SIN
    SIN(numeric)
`SIN ( numeric )`
Calculate the trigonometric sine. See also Java `Math.sin`. This method returns a double. Example: SIN(ANGLE) ### SINH
    SINH(numeric)
`SINH ( numeric )`
Calculate the hyperbolic sine. See also Java `Math.sinh`. This method returns a double. Example: SINH(ANGLE) ### TAN
    TAN(numeric)
`TAN ( numeric )`
Calculate the trigonometric tangent. See also Java `Math.tan`. This method returns a double. Example: TAN(ANGLE) ### TANH
    TANH(numeric)
`TANH ( numeric )`
Calculate the hyperbolic tangent. See also Java `Math.tanh`. This method returns a double. Example: TANH(X) ### ATAN2
    ATAN2(numeric, numeric)
`ATAN2 ( numeric , numeric )`
Calculate the angle when converting the rectangular coordinates to polar coordinates. See also Java `Math.atan2`. This method returns a double. Example: ATAN2(X, Y) ### BITAND
    BITAND(expression, expression)
`BITAND ( expression , expression )`
The bitwise `AND` operation. Arguments should have `TINYINT, SMALLINT, INTEGER, BIGINT, BINARY`, or `BINARY VARYING` data type. This function returns result of the same data type. For aggregate function see `BIT_AND_AGG`. Example: BITAND(A, B) ### BITOR
    BITOR(expression, expression)
`BITOR ( expression , expression )`
The bitwise `OR` operation. Arguments should have `TINYINT, SMALLINT, INTEGER, BIGINT, BINARY`, or `BINARY VARYING` data type. This function returns result of the same data type. For aggregate function see `BIT_OR_AGG`. Example: BITOR(A, B) ### BITXOR
    BITXOR(expression, expression)
`BITXOR ( expression , expression )`
Arguments should have `TINYINT, SMALLINT, INTEGER, BIGINT, BINARY`, or `BINARY VARYING` data type. This function returns result of the same data type. For aggregate function see `BIT_XOR_AGG`. Example: The bitwise XOR operation. ### BITNOT
    BITNOT(expression)
`BITNOT ( expression )`
The bitwise `NOT` operation. Argument should have `TINYINT, SMALLINT, INTEGER, BIGINT, BINARY`, or `BINARY VARYING` data type. This function returns result of the same data type. Example: BITNOT(A) ### BITNAND
    BITNAND(expression, expression)
`BITNAND ( expression , expression )`
The bitwise `NAND` operation equivalent to `BITNOT(BITAND(expression, expression))`. Arguments should have `TINYINT, SMALLINT, INTEGER, BIGINT, BINARY`, or `BINARY VARYING` data type. This function returns result of the same data type. For aggregate function see `BIT_NAND_AGG`. Example: BITNAND(A, B) ### BITNOR
    BITNOR(expression, expression)
`BITNOR ( expression , expression )`
The bitwise `NOR` operation equivalent to `BITNOT(BITOR(expression, expression))`. Arguments should have `TINYINT, SMALLINT, INTEGER, BIGINT, BINARY`, or `BINARY VARYING` data type. This function returns result of the same data type. For aggregate function see `BIT_NOR_AGG`. Example: BITNOR(A, B) ### BITXNOR
    BITXNOR(expression, expression)
`BITXNOR ( expression , expression )`
The bitwise `XNOR` operation equivalent to `BITNOT(BITXOR(expression, expression))`. Arguments should have `TINYINT, SMALLINT, INTEGER, BIGINT, BINARY`, or `BINARY VARYING` data type. This function returns result of the same data type. For aggregate function see `BIT_XNOR_AGG`. Example: BITXNOR(A, B) ### BITGET
    BITGET(expression, long)
`BITGET ( expression , long )`
Returns true if and only if the first argument has a bit set in the position specified by the second parameter. The first argument should have `TINYINT, SMALLINT, INTEGER, BIGINT, BINARY`, or `BINARY VARYING` data type. This method returns a boolean. The second argument is zero-indexed; the least significant bit has position 0. Example: BITGET(A, 1) ### BITCOUNT
    BITCOUNT(expression)
`BITCOUNT ( expression )`
Returns count of set bits in the specified value. Value should have `TINYINT, SMALLINT, INTEGER, BIGINT, BINARY`, or `BINARY VARYING` data type. This method returns a long. Example: BITCOUNT(A) ### LSHIFT
    LSHIFT(expression, long)
`LSHIFT ( expression , long )`
The bitwise signed left shift operation. Shifts the first argument by the number of bits given by the second argument. Argument should have `TINYINT, SMALLINT, INTEGER, BIGINT, BINARY`, or `BINARY VARYING` data type. This function returns result of the same data type. If number of bits is negative, a signed right shift is performed instead. For numeric values a sign bit is used for left-padding (with negative offset). If number of bits is equal to or larger than number of bits in value all bits are pushed out from the value. For binary string arguments signed and unsigned shifts return the same results. Example: LSHIFT(A, B) ### RSHIFT
    RSHIFT(expression, long)
`RSHIFT ( expression , long )`
The bitwise signed right shift operation. Shifts the first argument by the number of bits given by the second argument. Argument should have `TINYINT, SMALLINT, INTEGER, BIGINT, BINARY`, or `BINARY VARYING` data type. This function returns result of the same data type. If number of bits is negative, a signed left shift is performed instead. For numeric values a sign bit is used for left-padding (with positive offset). If number of bits is equal to or larger than number of bits in value all bits are pushed out from the value. For binary string arguments signed and unsigned shifts return the same results. Example: RSHIFT(A, B) ### ULSHIFT
    ULSHIFT(expression, long)
`ULSHIFT ( expression , long )`
The bitwise unsigned left shift operation. Shifts the first argument by the number of bits given by the second argument. Argument should have `TINYINT, SMALLINT, INTEGER, BIGINT, BINARY`, or `BINARY VARYING` data type. This function returns result of the same data type. If number of bits is negative, an unsigned right shift is performed instead. If number of bits is equal to or larger than number of bits in value all bits are pushed out from the value. For binary string arguments signed and unsigned shifts return the same results. Example: ULSHIFT(A, B) ### URSHIFT
    URSHIFT(expression, long)
`URSHIFT ( expression , long )`
The bitwise unsigned right shift operation. Shifts the first argument by the number of bits given by the second argument. Argument should have `TINYINT, SMALLINT, INTEGER, BIGINT, BINARY`, or `BINARY VARYING` data type. This function returns result of the same data type. If number of bits is negative, an unsigned left shift is performed instead. If number of bits is equal to or larger than number of bits in value all bits are pushed out from the value. For binary string arguments signed and unsigned shifts return the same results. Example: URSHIFT(A, B) ### ROTATELEFT
    ROTATELEFT(expression, long)
`ROTATELEFT ( expression , long )`
The bitwise left rotation operation. Rotates the first argument by the number of bits given by the second argument. Argument should have `TINYINT, SMALLINT, INTEGER, BIGINT, BINARY`, or `BINARY VARYING` data type. This function returns result of the same data type. Example: ROTATELEFT(A, B) ### ROTATERIGHT
    ROTATERIGHT(expression, long)
`ROTATERIGHT ( expression , long )`
The bitwise right rotation operation. Rotates the first argument by the number of bits given by the second argument. Argument should have `TINYINT, SMALLINT, INTEGER, BIGINT, BINARY`, or `BINARY VARYING` data type. This function returns result of the same data type. Example: ROTATERIGHT(A, B) ### MOD
    MOD(dividendNumeric, divisorNumeric)
`MOD ( dividendNumeric , divisorNumeric )`
The modulus expression. Result has the same type as divisor. Result is `NULL` if either of arguments is `NULL`. If divisor is 0, an exception is raised. Result has the same sign as dividend or is equal to 0. Usually arguments should have scale 0, but it isn't required by H2. Example: MOD(A, B) ### CEIL
    { CEIL | CEILING } (numeric)
| `CEIL` |
| `CEILING` |
| `( numeric )`
Returns the smallest integer value that is greater than or equal to the argument. This method returns value of the same type as argument, but with scale set to 0 and adjusted precision, if applicable. Example: CEIL(A) ### DEGREES
    DEGREES(numeric)
`DEGREES ( numeric )`
See also Java `Math.toDegrees`. This method returns a double. Example: DEGREES(A) ### EXP
    EXP(numeric)
`EXP ( numeric )`
See also Java `Math.exp`. This method returns a double. Example: EXP(A) ### FLOOR
    FLOOR(numeric)
`FLOOR ( numeric )`
Returns the largest integer value that is less than or equal to the argument. This method returns value of the same type as argument, but with scale set to 0 and adjusted precision, if applicable. Example: FLOOR(A) ### LN
    LN(numeric)
`LN ( numeric )`
Calculates the natural (base e) logarithm as a double value. Argument must be a positive numeric value. Example: LN(A) ### LOG
    LOG({baseNumeric, numeric | {numeric}})
`LOG (` |
`baseNumeric , numeric`
| `numeric` |
| `)`
Calculates the logarithm with specified base as a double value. Argument and base must be positive numeric values. Base cannot be equal to 1. The default base is e (natural logarithm), in the PostgreSQL mode the default base is base 10. In `MSSQLServer` mode the optional base is specified after the argument. Single-argument variant of `LOG` function is deprecated, use `LN` or `LOG10` instead. Example: LOG(2, A) ### LOG10
    LOG10(numeric)
`LOG10 ( numeric )`
Calculates the base 10 logarithm as a double value. Argument must be a positive numeric value. Example: LOG10(A) ### ORA_HASH
    ORA_HASH(expression [, bucketLong [, seedLong]])
`ORA_HASH ( expression` |
`, bucketLong` |
`, seedLong`
| `)`
Computes a hash value. Optional bucket argument determines the maximum returned value. This argument should be between 0 and 4294967295, default is 4294967295. Optional seed argument is combined with the given expression to return the different values for the same expression. This argument should be between 0 and 4294967295, default is 0. This method returns a long value between 0 and the specified or default bucket value inclusive. Example: ORA_HASH(A) ### RADIANS
    RADIANS(numeric)
`RADIANS ( numeric )`
See also Java `Math.toRadians`. This method returns a double. Example: RADIANS(A) ### SQRT
    SQRT(numeric)
`SQRT ( numeric )`
See also Java `Math.sqrt`. This method returns a double. Example: SQRT(A) ### PI
    PI()
`PI ( )`
See also Java `Math.PI`. This method returns a double. Example: PI() ### POWER
    POWER(numeric, numeric)
`POWER ( numeric , numeric )`
See also Java `Math.pow`. This method returns a double. Example: POWER(A, B) ### RAND
    { RAND | RANDOM } ( [ int ] )
| `RAND` |
| `RANDOM` |
| `(` |
| `int` |
| `)`
Calling the function without parameter returns the next a pseudo random number. Calling it with an parameter seeds the session's random number generator. This method returns a double between 0 (including) and 1 (excluding). Example: RAND() ### RANDOM_UUID
    { RANDOM_UUID | UUID } ()
| `RANDOM_UUID` |
| `UUID` |
| `( )`
Returns a new `UUID` with 122 pseudo random bits. Please note that using an index on randomly generated data will result on poor performance once there are millions of rows in a table. The reason is that the cache behavior is very bad with randomly distributed data. This is a problem for any database system. Example: RANDOM_UUID() ### ROUND
    ROUND(numeric [, digitsInt])
`ROUND ( numeric` |
`, digitsInt`
| `)`
Rounds to a number of fractional digits. This method returns value of the same type as argument, but with adjusted precision and scale, if applicable. Example: ROUND(N, 2) ### SECURE_RAND
    SECURE_RAND(int)
`SECURE_RAND ( int )`
Generates a number of cryptographically secure random numbers. This method returns bytes. Example: CALL SECURE_RAND(16) ### SIGN
    SIGN( { numeric | interval } )
`SIGN (` |
| `numeric` |
| `interval` |
| `)`
Returns -1 if the value is smaller than 0, 0 if zero or NaN, and otherwise 1. Example: SIGN(N) ### ENCRYPT
    ENCRYPT(algorithmString, keyBytes, dataBytes)
`ENCRYPT ( algorithmString , keyBytes , dataBytes )`
Encrypts data using a key. The supported algorithm is `AES`. The block size is 16 bytes. This method returns bytes. Example: CALL ENCRYPT('AES', '00', STRINGTOUTF8('Test')) ### DECRYPT
    DECRYPT(algorithmString, keyBytes, dataBytes)
`DECRYPT ( algorithmString , keyBytes , dataBytes )`
Decrypts data using a key. The supported algorithm is `AES`. The block size is 16 bytes. This method returns bytes. Example: CALL TRIM(CHAR(0) FROM UTF8TOSTRING( DECRYPT('AES', '00', '3fabb4de8f1ee2e97d7793bab2db1116'))) ### HASH
    HASH(algorithmString, expression [, iterationInt])
`HASH ( algorithmString , expression` |
`, iterationInt`
| `)`
Calculate the hash value using an algorithm, and repeat this process for a number of iterations. This function supports `MD5, SHA`-1, `SHA`-224, `SHA`-256, `SHA`-384, `SHA`-512, `SHA3`-224, `SHA3`-256, `SHA3`-384, and `SHA3`-512 algorithms. `SHA`-224, `SHA`-384, and `SHA`-512 may be unavailable in some `JREs`. `MD5` and `SHA`-1 algorithms should not be considered as secure. If this function is used to encrypt a password, a random salt should be concatenated with a password and this salt and result of the function should be stored to prevent a rainbow table attack and number of iterations should be large enough to slow down a dictionary or a brute force attack. This method returns bytes. Example: CALL HASH('SHA-256', 'Text', 1000) CALL HASH('SHA3-256', X'0102') ### TRUNC
    { TRUNC | TRUNCATE } ( { {numeric [, digitsInt] }
    | { timestamp | timestampWithTimeZone | date | timestampString } } )
| `TRUNC` |
| `TRUNCATE` |
| `(` |
`numeric` |
`, digitsInt`
| `timestamp` |
| `timestampWithTimeZone` |
| `date` |
| `timestampString` |
| `)`
When a numeric argument is specified, truncates it to a number of digits (to the next value closer to 0) and returns value of the same type as argument, but with adjusted precision and scale, if applicable. This function with datetime or string argument is deprecated, use `DATE_TRUNC` instead. When used with a timestamp, truncates the timestamp to a date (day) value and returns a timestamp with or without time zone depending on type of the argument. When used with a date, returns a timestamp at start of this date. When used with a timestamp as string, truncates the timestamp to a date (day) value and returns a timestamp without time zone. Example: TRUNCATE(N, 2) ### COMPRESS
    COMPRESS(dataBytes [, algorithmString])
`COMPRESS ( dataBytes` |
`, algorithmString`
| `)`
Compresses the data using the specified compression algorithm. Supported algorithms are: `LZF` (faster but lower compression; default), and `DEFLATE` (higher compression). Compression does not always reduce size. Very small objects and objects with little redundancy may get larger. This method returns bytes. Example: COMPRESS(STRINGTOUTF8('Test')) ### EXPAND
    EXPAND(bytes)
`EXPAND ( bytes )`
Expands data that was compressed using the `COMPRESS` function. This method returns bytes. Example: UTF8TOSTRING(EXPAND(COMPRESS(STRINGTOUTF8('Test')))) ### ZERO
    ZERO()
`ZERO ( )`
Returns the value 0. This function can be used even if numeric literals are disabled. Example: ZERO() ## String Functions ### ASCII
    ASCII(string)
`ASCII ( string )`
Returns the `ASCII` value of the first character in the string. This method returns an int. Example: ASCII('Hi') ### BIT_LENGTH
    BIT_LENGTH(bytes)
`BIT_LENGTH ( bytes )`
Returns the number of bits in a binary string. This method returns a long. Example: BIT_LENGTH(NAME) ### CHAR_LENGTH
    { CHAR_LENGTH | CHARACTER_LENGTH | { LENGTH } } ( string )
| `CHAR_LENGTH` |
| `CHARACTER_LENGTH` |
| `LENGTH` |
| `( string )`
Returns the number of characters in a character string. This method returns a long. Example: CHAR_LENGTH(NAME) ### OCTET_LENGTH
    OCTET_LENGTH(bytes)
`OCTET_LENGTH ( bytes )`
Returns the number of bytes in a binary string. This method returns a long. Example: OCTET_LENGTH(NAME) ### CHAR
    { CHAR | CHR } ( int )
| `CHAR` |
| `CHR` |
| `( int )`
Returns the character that represents the `ASCII` value. This method returns a string. Example: CHAR(65) ### CONCAT
    CONCAT(string, string [,...])
`CONCAT ( string , string` |
| `, ...` |
| `)`
Combines strings. Unlike with the operator `||, NULL` parameters are ignored, and do not cause the result to become `NULL`. If all parameters are `NULL` the result is an empty string. This method returns a string. Example: CONCAT(NAME, '!') ### CONCAT_WS
    CONCAT_WS(separatorString, string, string [,...])
`CONCAT_WS ( separatorString , string , string` |
| `, ...` |
| `)`
Combines strings with separator. If separator is `NULL` it is treated like an empty string. Other `NULL` parameters are ignored. Remaining non-`NULL` parameters, if any, are concatenated with the specified separator. If there are no remaining parameters the result is an empty string. This method returns a string. Example: CONCAT_WS(',', NAME, '!') ### DIFFERENCE
    DIFFERENCE(string, string)
`DIFFERENCE ( string , string )`
Returns the difference between the sounds of two strings. The difference is calculated as a number of matched characters in the same positions in `SOUNDEX` representations of arguments. This method returns an int between 0 and 4 inclusive, or null if any of its parameters is null. Note that value of 0 means that strings are not similar to each other. Value of 4 means that strings are fully similar to each other (have the same `SOUNDEX` representation). Example: DIFFERENCE(T1.NAME, T2.NAME) ### HEXTORAW
    HEXTORAW(string)
`HEXTORAW ( string )`
Converts a hex representation of a string to a string. 4 hex characters per string character are used. Example: HEXTORAW(DATA) ### RAWTOHEX
    RAWTOHEX({string|bytes})
`RAWTOHEX (` |
| `string` |
| `bytes` |
| `)`
Converts a string or bytes to the hex representation. 4 hex characters per string character are used. This method returns a string. Example: RAWTOHEX(DATA) ### INSERT Function
    INSERT(originalString, startInt, lengthInt, addString)
`INSERT ( originalString , startInt , lengthInt , addString )`
Inserts a additional string into the original string at a specified start position. The length specifies the number of characters that are removed at the start position in the original string. This method returns a string. Example: INSERT(NAME, 1, 1, ' ') ### LOWER
    { LOWER | { LCASE } } ( string )
| `LOWER` |
| `LCASE` |
| `( string )`
Converts a string to lowercase. Example: LOWER(NAME) ### UPPER
    { UPPER | { UCASE } } ( string )
| `UPPER` |
| `UCASE` |
| `( string )`
Converts a string to uppercase. Example: UPPER(NAME) ### LEFT
    LEFT(string, int)
`LEFT ( string , int )`
Returns the leftmost number of characters. Example: LEFT(NAME, 3) ### RIGHT
    RIGHT(string, int)
`RIGHT ( string , int )`
Returns the rightmost number of characters. Example: RIGHT(NAME, 3) ### LOCATE
    { LOCATE(searchString, string [, startInt]) }
    | { INSTR(string, searchString, [, startInt]) }
    | { POSITION(searchString, string) }
`LOCATE ( searchString , string` |
`, startInt`
| `)`
`INSTR ( string , searchString ,` |
`, startInt`
| `)`
`POSITION ( searchString , string )`
Returns the location of a search string in a string. If a start position is used, the characters before it are ignored. If position is negative, the rightmost location is returned. 0 is returned if the search string is not found. Please note this function is case sensitive, even if the parameters are not. Example: LOCATE('.', NAME) ### LPAD
    LPAD(string, int[, paddingString])
`LPAD ( string , int` |
`, paddingString`
| `)`
Left pad the string to the specified length. If the length is shorter than the string, it will be truncated at the end. If the padding string is not set, spaces will be used. Example: LPAD(AMOUNT, 10, '*') ### RPAD
    RPAD(string, int[, paddingString])
`RPAD ( string , int` |
`, paddingString`
| `)`
Right pad the string to the specified length. If the length is shorter than the string, it will be truncated. If the padding string is not set, spaces will be used. Example: RPAD(TEXT, 10, '-') ### LTRIM
    LTRIM(string [, charactersToTrimString])
`LTRIM ( string` |
`, charactersToTrimString`
| `)`
Removes all leading spaces or other specified characters from a string, multiple characters can be specified. Example: LTRIM(NAME) LTRIM(NAME, ' _~'); ### RTRIM
    RTRIM(string [, charactersToTrimString])
`RTRIM ( string` |
`, charactersToTrimString`
| `)`
Removes all trailing spaces or other specified characters from a string, multiple characters can be specified. Example: RTRIM(NAME) RTRIM(NAME, ' _~'); ### BTRIM
    BTRIM(string [, charactersToTrimString])
`BTRIM ( string` |
`, charactersToTrimString`
| `)`
Removes all leading and trailing spaces or other specified characters from a string, multiple characters can be specified. Example: BTRIM(NAME) BTRIM(NAME, ' _~'); ### TRIM
    TRIM ( [ [ LEADING | TRAILING | BOTH ] [ characterToTrimString ] FROM ] string )
`TRIM (` |
| `LEADING` |
| `TRAILING` |
| `BOTH` |
| `characterToTrimString` |
| `FROM`
| `string )`
Removes all leading spaces, trailing spaces, or spaces at both ends from a string. If character to trim is specified, these characters are removed instead of spaces, only one character can be specified. To trim multiple different characters use `LTRIM`, `RTRIM`, or `BTRIM`. If neither `LEADING, TRAILING`, nor `BOTH` are specified, `BOTH` is implicit. Example: TRIM(NAME) TRIM(LEADING FROM NAME) TRIM(BOTH '_' FROM NAME) ### REGEXP_REPLACE
    REGEXP_REPLACE(inputString, regexString, replacementString [, flagsString])
`REGEXP_REPLACE ( inputString , regexString , replacementString` |
`, flagsString`
| `)`
Replaces each substring that matches a regular expression. For details, see the Java `String.replaceAll()` method. If any parameter is null (except optional flagsString parameter), the result is null. Flags values are limited to 'i', 'c', 'n', 'm'. Other symbols cause exception. Multiple symbols could be used in one flagsString parameter (like 'im'). Later flags override first ones, for example 'ic' is equivalent to case sensitive matching 'c'. 'i' enables case insensitive matching (Pattern.`CASE_INSENSITIVE`) 'c' disables case insensitive matching (Pattern.`CASE_INSENSITIVE`) 'n' allows the period to match the newline character (Pattern.`DOTALL`) 'm' enables multiline mode (Pattern.`MULTILINE`) Example: REGEXP_REPLACE('Hello World', ' +', ' ') REGEXP_REPLACE('Hello WWWWorld', 'w+', 'W', 'i') ### REGEXP_LIKE
    REGEXP_LIKE(inputString, regexString [, flagsString])
`REGEXP_LIKE ( inputString , regexString` |
`, flagsString`
| `)`
Matches string to a regular expression. For details, see the Java `Matcher.find()` method. If any parameter is null (except optional flagsString parameter), the result is null. Flags values are limited to 'i', 'c', 'n', 'm'. Other symbols cause exception. Multiple symbols could be used in one flagsString parameter (like 'im'). Later flags override first ones, for example 'ic' is equivalent to case sensitive matching 'c'. 'i' enables case insensitive matching (Pattern.`CASE_INSENSITIVE`) 'c' disables case insensitive matching (Pattern.`CASE_INSENSITIVE`) 'n' allows the period to match the newline character (Pattern.`DOTALL`) 'm' enables multiline mode (Pattern.`MULTILINE`) Example: REGEXP_LIKE('Hello World', '[A-Z ]*', 'i') ### REGEXP_SUBSTR
    REGEXP_SUBSTR(inputString, regexString [, positionInt, occurrenceInt, flagsString, groupInt])
`REGEXP_SUBSTR ( inputString , regexString` |
`, positionInt , occurrenceInt , flagsString , groupInt`
| `)`
Matches string to a regular expression and returns the matched substring. For details, see the java.util.regex.Pattern and related functionality. The parameter position specifies where in inputString the match should start. Occurrence indicates which occurrence of pattern in inputString to search for. Flags values are limited to 'i', 'c', 'n', 'm'. Other symbols cause exception. Multiple symbols could be used in one flagsString parameter (like 'im'). Later flags override first ones, for example 'ic' is equivalent to case sensitive matching 'c'. 'i' enables case insensitive matching (Pattern.`CASE_INSENSITIVE`) 'c' disables case insensitive matching (Pattern.`CASE_INSENSITIVE`) 'n' allows the period to match the newline character (Pattern.`DOTALL`) 'm' enables multiline mode (Pattern.`MULTILINE`) If the pattern has groups, the group parameter can be used to specify which group to return. Example: REGEXP_SUBSTR('2020-10-01', '\d{4}') REGEXP_SUBSTR('2020-10-01', '(\d{4})-(\d{2})-(\d{2})', 1, 1, NULL, 2) ### REPEAT
    REPEAT(string, int)
`REPEAT ( string , int )`
Returns a string repeated some number of times. Example: REPEAT(NAME || ' ', 10) ### REPLACE
    REPLACE(string, searchString [, replacementString])
`REPLACE ( string , searchString` |
`, replacementString`
| `)`
Replaces all occurrences of a search string in a text with another string. If no replacement is specified, the search string is removed from the original string. If any parameter is null, the result is null. Example: REPLACE(NAME, ' ') ### SOUNDEX
    SOUNDEX(string)
`SOUNDEX ( string )`
Returns a four character upper-case code representing the sound of a string as pronounced in English. This method returns a string, or null if parameter is null. See https://en.wikipedia.org/wiki/Soundex for more information. Example: SOUNDEX(NAME) ### SPACE
    SPACE(int)
`SPACE ( int )`
Returns a string consisting of a number of spaces. Example: SPACE(80) ### STRINGDECODE
    STRINGDECODE(string)
`STRINGDECODE ( string )`
Converts a encoded string using the Java string literal encoding format. Special characters are \b, \t, \n, \f, \r, \", \\, \<octal>, \u<unicode>. This method returns a string. Example: CALL STRINGENCODE(STRINGDECODE('Lines 1\nLine 2')) ### STRINGENCODE
    STRINGENCODE(string)
`STRINGENCODE ( string )`
Encodes special characters in a string using the Java string literal encoding format. Special characters are \b, \t, \n, \f, \r, \", \\, \<octal>, \u<unicode>. This method returns a string. Example: CALL STRINGENCODE(STRINGDECODE('Lines 1\nLine 2')) ### STRINGTOUTF8
    STRINGTOUTF8(string)
`STRINGTOUTF8 ( string )`
Encodes a string to a byte array using the `UTF8` encoding format. This method returns bytes. Example: CALL UTF8TOSTRING(STRINGTOUTF8('This is a test')) ### SUBSTRING
    SUBSTRING ( {string|bytes} FROM startInt [ FOR lengthInt ] )
    | { { SUBSTRING | SUBSTR } ( {string|bytes}, startInt [, lengthInt ] ) }
`SUBSTRING (` |
| `string` |
| `bytes` |
| `FROM startInt` |
`FOR lengthInt`
| `)`
| `SUBSTRING` |
| `SUBSTR` |
| `(` |
| `string` |
| `bytes` |
| `, startInt` |
`, lengthInt`
| `)`
Returns a substring of a string starting at a position. If the start index is negative, then the start index is relative to the end of the string. The length is optional. Example: CALL SUBSTRING('[Hello]' FROM 2 FOR 5); CALL SUBSTRING('hour' FROM 2); ### UTF8TOSTRING
    UTF8TOSTRING(bytes)
`UTF8TOSTRING ( bytes )`
Decodes a byte array in the `UTF8` format to a string. Example: CALL UTF8TOSTRING(STRINGTOUTF8('This is a test')) ### QUOTE_IDENT
    QUOTE_IDENT(string)
`QUOTE_IDENT ( string )`
Quotes the specified identifier. Identifier is surrounded by double quotes. If identifier contains double quotes they are repeated twice. Example: QUOTE_IDENT('Column 1') ### XMLATTR
    XMLATTR(nameString, valueString)
`XMLATTR ( nameString , valueString )`
Creates an XML attribute element of the form `name=value`. The value is encoded as XML text. This method returns a string. Example: CALL XMLNODE('a', XMLATTR('href', 'https://h2database.com')) ### XMLNODE
    XMLNODE(elementString [, attributesString [, contentString [, indentBoolean]]])
`XMLNODE ( elementString` |
`, attributesString` |
`, contentString` |
`, indentBoolean`
| `)`
Create an XML node element. An empty or null attribute string means no attributes are set. An empty or null content string means the node is empty. The content is indented by default if it contains a newline. This method returns a string. Example: CALL XMLNODE('a', XMLATTR('href', 'https://h2database.com'), 'H2') ### XMLCOMMENT
    XMLCOMMENT(commentString)
`XMLCOMMENT ( commentString )`
Creates an XML comment. Two dashes (`--`) are converted to `- -`. This method returns a string. Example: CALL XMLCOMMENT('Test') ### XMLCDATA
    XMLCDATA(valueString)
`XMLCDATA ( valueString )`
Creates an `XML CDATA` element. If the value contains `]]>`, an XML text element is created instead. This method returns a string. Example: CALL XMLCDATA('data') ### XMLSTARTDOC
    XMLSTARTDOC()
`XMLSTARTDOC ( )`
Returns the XML declaration. The result is always `<?xml version=`1.0`?>`. Example: CALL XMLSTARTDOC() ### XMLTEXT
    XMLTEXT(valueString [, escapeNewlineBoolean])
`XMLTEXT ( valueString` |
`, escapeNewlineBoolean`
| `)`
Creates an XML text element. If enabled, newline and linefeed is converted to an XML entity (&#). This method returns a string. Example: CALL XMLTEXT('test') ### TO_CHAR
    TO_CHAR(value [, formatString[, nlsParamString]])
`TO_CHAR ( value` |
`, formatString` |
`, nlsParamString`
| `)`
Oracle-compatible `TO_CHAR` function that can format a timestamp, a number, or text. Example: CALL TO_CHAR(TIMESTAMP '2010-01-01 00:00:00', 'DD MON, YYYY') ### TRANSLATE
    TRANSLATE(value, searchString, replacementString)
`TRANSLATE ( value , searchString , replacementString )`
Oracle-compatible `TRANSLATE` function that replaces a sequence of characters in a string with another set of characters. Example: CALL TRANSLATE('Hello world', 'eo', 'EO') ## Time and Date Functions ### CURRENT_DATE
    CURRENT_DATE
`CURRENT_DATE` Returns the current date. These functions return the same value within a transaction (default) or within a command depending on database mode. `SET TIME ZONE` command reevaluates the value for these functions using the same original `UTC` timestamp of transaction. Example: CURRENT_DATE ### CURRENT_TIME
    CURRENT_TIME [ (int) ]
`CURRENT_TIME` |
`( int )`
Returns the current time with time zone. If fractional seconds precision is specified it should be from 0 to 9, 0 is default. The specified value can be used only to limit precision of a result. The actual maximum available precision depends on operating system and `JVM` and can be 3 (milliseconds) or higher. Higher precision is not available before Java 9. This function returns the same value within a transaction (default) or within a command depending on database mode. `SET TIME ZONE` command reevaluates the value for this function using the same original `UTC` timestamp of transaction. Example: CURRENT_TIME CURRENT_TIME(9) ### CURRENT_TIMESTAMP
    CURRENT_TIMESTAMP [ (int) ]
`CURRENT_TIMESTAMP` |
`( int )`
Returns the current timestamp with time zone. Time zone offset is set to a current time zone offset. If fractional seconds precision is specified it should be from 0 to 9, 6 is default. The specified value can be used only to limit precision of a result. The actual maximum available precision depends on operating system and `JVM` and can be 3 (milliseconds) or higher. Higher precision is not available before Java 9. This function returns the same value within a transaction (default) or within a command depending on database mode. `SET TIME ZONE` command reevaluates the value for this function using the same original `UTC` timestamp of transaction. Example: CURRENT_TIMESTAMP CURRENT_TIMESTAMP(9) ### LOCALTIME
    LOCALTIME [ (int) ]
`LOCALTIME` |
`( int )`
Returns the current time without time zone. If fractional seconds precision is specified it should be from 0 to 9, 0 is default. The specified value can be used only to limit precision of a result. The actual maximum available precision depends on operating system and `JVM` and can be 3 (milliseconds) or higher. Higher precision is not available before Java 9. These functions return the same value within a transaction (default) or within a command depending on database mode. `SET TIME ZONE` command reevaluates the value for these functions using the same original `UTC` timestamp of transaction. Example: LOCALTIME LOCALTIME(9) ### LOCALTIMESTAMP
    LOCALTIMESTAMP [ (int) ]
`LOCALTIMESTAMP` |
`( int )`
Returns the current timestamp without time zone. If fractional seconds precision is specified it should be from 0 to 9, 6 is default. The specified value can be used only to limit precision of a result. The actual maximum available precision depends on operating system and `JVM` and can be 3 (milliseconds) or higher. Higher precision is not available before Java 9. The returned value has date and time without time zone information. If time zone has `DST` transitions the returned values are ambiguous during transition from `DST` to normal time. For absolute timestamps use the `CURRENT_TIMESTAMP` function and `TIMESTAMP WITH TIME ZONE` data type. These functions return the same value within a transaction (default) or within a command depending on database mode. `SET TIME ZONE` reevaluates the value for these functions using the same original `UTC` timestamp of transaction. Example: LOCALTIMESTAMP LOCALTIMESTAMP(9) ### DATEADD
    { DATEADD| TIMESTAMPADD } (datetimeField, addIntLong, dateAndTime)
| `DATEADD` |
| `TIMESTAMPADD` |
`( datetimeField , addIntLong , dateAndTime )`
Adds units to a date-time value. The datetimeField indicates the unit. Use negative values to subtract units. addIntLong may be a long value when manipulating milliseconds, microseconds, or nanoseconds otherwise its range is restricted to int. This method returns a value with the same type as specified value if unit is compatible with this value. If specified field is a `HOUR, MINUTE, SECOND, MILLISECOND`, etc and value is a `DATE` value `DATEADD` returns combined `TIMESTAMP`. Fields `DAY, MONTH, YEAR, WEEK`, etc are not allowed for `TIME` values. Fields `TIMEZONE_HOUR, TIMEZONE_MINUTE`, and `TIMEZONE_SECOND` are only allowed for `TIMESTAMP WITH TIME ZONE` values. Example: DATEADD(MONTH, 1, DATE '2001-01-31') ### DATEDIFF
    { DATEDIFF | TIMESTAMPDIFF } (datetimeField, aDateAndTime, bDateAndTime)
| `DATEDIFF` |
| `TIMESTAMPDIFF` |
`( datetimeField , aDateAndTime , bDateAndTime )`
Returns the number of crossed unit boundaries between two date/time values. This method returns a long. The datetimeField indicates the unit. Only `TIMEZONE_HOUR, TIMEZONE_MINUTE`, and `TIMEZONE_SECOND` fields use the time zone offset component. With all other fields if date/time values have time zone offset component it is ignored. Example: DATEDIFF(YEAR, T1.CREATED, T2.CREATED) ### DATE_TRUNC
    DATE_TRUNC(datetimeField, dateAndTime)
`DATE_TRUNC ( datetimeField , dateAndTime )`
Truncates the specified date-time value to the specified field. Example: DATE_TRUNC(DAY, TIMESTAMP '2010-01-03 10:40:00'); ### LAST_DAY
    LAST_DAY(date | timestamp | timestampWithTimeZone | string)
`LAST_DAY ( date`
| `timestamp` |
| `timestampWithTimeZone` |
`string )`
Returns the last day of the month that contains the specified date-time value. This function returns a date. Example: LAST_DAY(DAY, DATE '2020-02-05'); ### DAYNAME
    DAYNAME(dateAndTime)
`DAYNAME ( dateAndTime )`
Returns the name of the day (in English). Example: DAYNAME(CREATED) ### DAY_OF_MONTH
    DAY_OF_MONTH({dateAndTime|interval})
`DAY_OF_MONTH (` |
| `dateAndTime` |
| `interval` |
| `)`
Returns the day of the month (1-31). This function is deprecated, use `EXTRACT` instead of it. Example: DAY_OF_MONTH(CREATED) ### DAY_OF_WEEK
    DAY_OF_WEEK(dateAndTime)
`DAY_OF_WEEK ( dateAndTime )`
Returns the day of the week (1-7), locale-specific. This function is deprecated, use `EXTRACT` instead of it. Example: DAY_OF_WEEK(CREATED) ### ISO_DAY_OF_WEEK
    ISO_DAY_OF_WEEK(dateAndTime)
`ISO_DAY_OF_WEEK ( dateAndTime )`
Returns the `ISO` day of the week (1 means Monday). This function is deprecated, use `EXTRACT` instead of it. Example: ISO_DAY_OF_WEEK(CREATED) ### DAY_OF_YEAR
    DAY_OF_YEAR({dateAndTime|interval})
`DAY_OF_YEAR (` |
| `dateAndTime` |
| `interval` |
| `)`
Returns the day of the year (1-366). This function is deprecated, use `EXTRACT` instead of it. Example: DAY_OF_YEAR(CREATED) ### EXTRACT
    EXTRACT ( datetimeField FROM { dateAndTime | interval })
`EXTRACT ( datetimeField FROM` |
| `dateAndTime` |
| `interval` |
| `)`
Returns a value of the specific time unit from a date/time value. This method returns a numeric value with `EPOCH` field and an int for all other fields. Example: EXTRACT(SECOND FROM CURRENT_TIMESTAMP) ### FORMATDATETIME
    FORMATDATETIME ( dateAndTime, formatString
    [ , localeString [ , timeZoneString ] ] )
`FORMATDATETIME ( dateAndTime , formatString`
`, localeString` |
`, timeZoneString`
| `)`
Formats a date, time or timestamp as a string. The most important format characters are: y year, M month, d day, H hour, m minute, s second. For details of the format, see `java.time.format.DateTimeFormatter`. Allowed format characters depend on data type of passed date/time value. If timeZoneString is specified, it is used in formatted string if formatString has time zone. For `TIME` and `TIME WITH TIME ZONE` values the specified time zone must have a fixed offset. If `TIME WITH TIME ZONE` is passed and timeZoneString is specified, the time is converted to the specified time zone offset and its `UTC` value is preserved. If `TIMESTAMP WITH TIME ZONE` is passed and timeZoneString is specified, the timestamp is converted to the specified time zone and its `UTC` value is preserved. This method returns a string. See also cast specification. Example: CALL FORMATDATETIME(TIMESTAMP '2001-02-03 04:05:06', 'EEE, d MMM yyyy HH:mm:ss z', 'en', 'GMT') ### HOUR
    HOUR({dateAndTime|interval})
`HOUR (` |
| `dateAndTime` |
| `interval` |
| `)`
Returns the hour (0-23) from a date/time value. This function is deprecated, use `EXTRACT` instead of it. Example: HOUR(CREATED) ### MINUTE
    MINUTE({dateAndTime|interval})
`MINUTE (` |
| `dateAndTime` |
| `interval` |
| `)`
Returns the minute (0-59) from a date/time value. This function is deprecated, use `EXTRACT` instead of it. Example: MINUTE(CREATED) ### MONTH
    MONTH({dateAndTime|interval})
`MONTH (` |
| `dateAndTime` |
| `interval` |
| `)`
Returns the month (1-12) from a date/time value. This function is deprecated, use `EXTRACT` instead of it. Example: MONTH(CREATED) ### MONTHNAME
    MONTHNAME(dateAndTime)
`MONTHNAME ( dateAndTime )`
Returns the name of the month (in English). Example: MONTHNAME(CREATED) ### PARSEDATETIME
    PARSEDATETIME(string, formatString
    [, localeString [, timeZoneString]])
`PARSEDATETIME ( string , formatString`
`, localeString` |
`, timeZoneString`
| `)`
Parses a string and returns a `TIMESTAMP WITH TIME ZONE` value. The most important format characters are: y year, M month, d day, H hour, m minute, s second. For details of the format, see `java.time.format.DateTimeFormatter`. If timeZoneString is specified, it is used as default. See also cast specification. Example: CALL PARSEDATETIME('Sat, 3 Feb 2001 03:05:06 GMT', 'EEE, d MMM yyyy HH:mm:ss z', 'en', 'GMT') ### QUARTER
    QUARTER(dateAndTime)
`QUARTER ( dateAndTime )`
Returns the quarter (1-4) from a date/time value. This function is deprecated, use `EXTRACT` instead of it. Example: QUARTER(CREATED) ### SECOND
    SECOND(dateAndTime)
`SECOND ( dateAndTime )`
Returns the second (0-59) from a date/time value. This function is deprecated, use `EXTRACT` instead of it. Example: SECOND(CREATED|interval) ### WEEK
    WEEK(dateAndTime)
`WEEK ( dateAndTime )`
Returns the week (1-53) from a date/time value. This function is deprecated, use `EXTRACT` instead of it. This function uses the current system locale. Example: WEEK(CREATED) ### ISO_WEEK
    ISO_WEEK(dateAndTime)
`ISO_WEEK ( dateAndTime )`
Returns the `ISO` week (1-53) from a date/time value. This function is deprecated, use `EXTRACT` instead of it. This function uses the `ISO` definition when first week of year should have at least four days and week is started with Monday. Example: ISO_WEEK(CREATED) ### YEAR
    YEAR({dateAndTime|interval})
`YEAR (` |
| `dateAndTime` |
| `interval` |
| `)`
Returns the year from a date/time value. This function is deprecated, use `EXTRACT` instead of it. Example: YEAR(CREATED) ### ISO_YEAR
    ISO_YEAR(dateAndTime)
`ISO_YEAR ( dateAndTime )`
Returns the `ISO` week year from a date/time value. This function is deprecated, use `EXTRACT` instead of it. Example: ISO_YEAR(CREATED) ## System Functions ### ABORT_SESSION
    ABORT_SESSION(sessionInt)
`ABORT_SESSION ( sessionInt )`
Cancels the currently executing statement of another session. Closes the session and releases the allocated resources. Returns true if the session was closed, false if no session with the given id was found. If a client was connected while its session was aborted it will see an error. Admin rights are required to execute this command. Example: CALL ABORT_SESSION(3) ### ARRAY_GET
    ARRAY_GET(arrayExpression, indexExpression)
`ARRAY_GET ( arrayExpression , indexExpression )`
Returns element at the specified 1-based index from an array. This function is deprecated, use [array element reference](https://www.h2database.com/html/grammar.html#array_element_reference) instead of it. Returns `NULL` if array or index is `NULL`. Example: CALL ARRAY_GET(ARRAY['Hello', 'World'], 2) ### CARDINALITY
    { CARDINALITY | { ARRAY_LENGTH } } (arrayExpression)
| `CARDINALITY` |
| `ARRAY_LENGTH` |
| `( arrayExpression )`
Returns the length of an array or `JSON` array. Returns `NULL` if the specified array is `NULL`. Example: CALL CARDINALITY(ARRAY['Hello', 'World']) CALL CARDINALITY(JSON '[1, 2, 3]') ### ARRAY_CONTAINS
    ARRAY_CONTAINS(arrayExpression, value)
`ARRAY_CONTAINS ( arrayExpression , value )`
Returns a boolean `TRUE` if the array contains the value or `FALSE` if it does not contain it. Returns `NULL` if the specified array is `NULL`. Example: CALL ARRAY_CONTAINS(ARRAY['Hello', 'World'], 'Hello') ### ARRAY_CAT
    ARRAY_CAT(arrayExpression, arrayExpression)
`ARRAY_CAT ( arrayExpression , arrayExpression )`
Returns the concatenation of two arrays. This function is deprecated, use `||` instead of it. Returns `NULL` if any parameter is `NULL`. Example: CALL ARRAY_CAT(ARRAY[1, 2], ARRAY[3, 4]) ### ARRAY_APPEND
    ARRAY_APPEND(arrayExpression, value)
`ARRAY_APPEND ( arrayExpression , value )`
Append an element to the end of an array. This function is deprecated, use `||` instead of it. Returns `NULL` if any parameter is `NULL`. Example: CALL ARRAY_APPEND(ARRAY[1, 2], 3) ### ARRAY_MAX_CARDINALITY
    ARRAY_MAX_CARDINALITY(arrayExpression)
`ARRAY_MAX_CARDINALITY ( arrayExpression )`
Returns the maximum allowed array cardinality (length) of the declared data type of argument. Example: SELECT ARRAY_MAX_CARDINALITY(COL1) FROM TEST FETCH FIRST ROW ONLY; ### TRIM_ARRAY
    TRIM_ARRAY(arrayExpression, int)
`TRIM_ARRAY ( arrayExpression , int )`
Removes the specified number of elements from the end of the array. Returns `NULL` if second parameter is `NULL` or if first parameter is `NULL` and second parameter is not negative. Throws exception if second parameter is negative or larger than number of elements in array. Otherwise returns the truncated array. Example: CALL TRIM_ARRAY(ARRAY[1, 2, 3, 4], 1) ### ARRAY_SLICE
    ARRAY_SLICE(arrayExpression, lowerBoundInt, upperBoundInt)
`ARRAY_SLICE ( arrayExpression , lowerBoundInt , upperBoundInt )`
Returns elements from the array as specified by the lower and upper bound parameters. Both parameters are inclusive and the first element has index 1, i.e. `ARRAY_SLICE`(a, 2, 2) has only the second element. Returns `NULL` if any parameter is `NULL` or if an index is out of bounds. Example: CALL ARRAY_SLICE(ARRAY[1, 2, 3, 4], 1, 3) ### AUTOCOMMIT
    AUTOCOMMIT()
`AUTOCOMMIT ( )`
Returns true if auto commit is switched on for this session. Example: AUTOCOMMIT() ### CANCEL_SESSION
    CANCEL_SESSION(sessionInt)
`CANCEL_SESSION ( sessionInt )`
Cancels the currently executing statement of another session. Returns true if the statement was canceled, false if the session is closed or no statement is currently executing. Admin rights are required to execute this command. Example: CANCEL_SESSION(3) ### CASEWHEN Function
    CASEWHEN(boolean, aValue, bValue)
`CASEWHEN ( boolean , aValue , bValue )`
Returns 'aValue' if the boolean expression is true, otherwise 'bValue'. This function is deprecated, use `CASE` instead of it. Example: CASEWHEN(ID=1, 'A', 'B') ### COALESCE
    { COALESCE | { NVL } } (aValue, bValue [,...])
    | IFNULL(aValue, bValue)
| `COALESCE` |
| `NVL` |
| `( aValue , bValue` |
| `, ...` |
| `)`
`IFNULL ( aValue , bValue )`
Returns the first value that is not null. Example: COALESCE(A, B, C) ### CONVERT
    CONVERT(value, dataTypeOrDomain)
`CONVERT ( value , dataTypeOrDomain )`
Converts a value to another data type. This function is deprecated, use `CAST` instead of it. Example: CONVERT(NAME, INT) ### CURRVAL
    CURRVAL( [ schemaNameString, ] sequenceString )
`CURRVAL (` |
`schemaNameString ,`
| `sequenceString )`
Returns the latest generated value of the sequence for the current session. Current value may only be requested after generation of the sequence value in the current session. This method exists only for compatibility, when it isn't required use `CURRENT VALUE FOR` sequenceName instead. If the schema name is not set, the current schema is used. When sequence is not found, the uppercase name is also checked. This method returns a long. Example: CURRVAL('TEST_SEQ') ### CSVWRITE
    CSVWRITE ( fileNameString, queryString [, csvOptions [, lineSepString] ] )
`CSVWRITE ( fileNameString , queryString` |
`, csvOptions` |
`, lineSepString`
| `)`
Writes a `CSV` (comma separated values). The file is overwritten if it exists. If only a file name is specified, it will be written to the current working directory. For each parameter, `NULL` means the default value should be used. The default charset is the default value for this system, and the default field separator is a comma. The values are converted to text using the default string representation; if another conversion is required you need to change the select statement accordingly. The parameter nullString is used when writing `NULL` (by default nothing is written when `NULL` appears). The default line separator is the default value for this system (system property `line.separator`). The returned value is the number or rows written. Admin rights are required to execute this command. Example: CALL CSVWRITE('data/test.csv', 'SELECT * FROM TEST'); CALL CSVWRITE('data/test2.csv', 'SELECT * FROM TEST', 'charset=UTF-8 fieldSeparator=|'); -- Write a tab-separated file CALL CSVWRITE('data/test.tsv', 'SELECT * FROM TEST', 'charset=UTF-8 fieldSeparator=' || CHAR(9)); ### CURRENT_SCHEMA
    CURRENT_SCHEMA | SCHEMA()
| `CURRENT_SCHEMA` |
`SCHEMA ( )`
Returns the name of the default schema for this session. Example: CALL CURRENT_SCHEMA ### CURRENT_CATALOG
    CURRENT_CATALOG | DATABASE()
| `CURRENT_CATALOG` |
`DATABASE ( )`
Returns the name of the database. Example: CALL CURRENT_CATALOG ### DATABASE_PATH
    DATABASE_PATH()
`DATABASE_PATH ( )`
Returns the directory of the database files and the database name, if it is file based. Returns `NULL` otherwise. Example: CALL DATABASE_PATH(); ### DATA_TYPE_SQL
    DATA_TYPE_SQL
    (objectSchemaString, objectNameString, objectTypeString, typeIdentifierString)
`DATA_TYPE_SQL`
`( objectSchemaString , objectNameString , objectTypeString , typeIdentifierString )`
Returns SQL representation of data type of the specified constant, domain, table column, routine result or argument. For constants object type is '`CONSTANT`' and type identifier is the value of `INFORMATION_SCHEMA.CONSTANTS.DTD_IDENTIFIER`. For domains object type is '`DOMAIN`' and type identifier is the value of `INFORMATION_SCHEMA.DOMAINS.DTD_IDENTIFIER`. For columns object type is '`TABLE`' and type identifier is the value of `INFORMATION_SCHEMA.COLUMNS.DTD_IDENTIFIER`. For routines object name is the value of `INFORMATION_SCHEMA.ROUTINES.SPECIFIC_NAME`, object type is '`ROUTINE`', and type identifier is the value of `INFORMATION_SCHEMA.ROUTINES.DTD_IDENTIFIER` for data type of the result and the value of `INFORMATION_SCHEMA.PARAMETERS.DTD_IDENTIFIER` for data types of arguments. Aggregate functions aren't supported by this function, because their data type isn't statically known. This function returns `NULL` if any argument is `NULL`, object type is not valid, or object isn't found. Example: DATA_TYPE_SQL('PUBLIC', 'C', 'CONSTANT', 'TYPE') DATA_TYPE_SQL('PUBLIC', 'D', 'DOMAIN', 'TYPE') DATA_TYPE_SQL('PUBLIC', 'T', 'TABLE', '1') DATA_TYPE_SQL('PUBLIC', 'R_1', 'ROUTINE', 'RESULT') DATA_TYPE_SQL('PUBLIC', 'R_1', 'ROUTINE', '1') COALESCE( QUOTE_IDENT(DOMAIN_SCHEMA) || '.' || QUOTE_IDENT(DOMAIN_NAME), DATA_TYPE_SQL(TABLE_SCHEMA, TABLE_NAME, 'TABLE', DTD_IDENTIFIER)) ### DB_OBJECT_ID
    DB_OBJECT_ID({{'ROLE'|'SETTING'|'SCHEMA'|'USER'}, objectNameString
    | {'CONSTANT'|'CONSTRAINT'|'DOMAIN'|'INDEX'|'ROUTINE'|'SEQUENCE'
        |'SYNONYM'|'TABLE'|'TRIGGER'}, schemaNameString, objectNameString })
`DB_OBJECT_ID (` |
`' ROLE '`
`' SETTING '`
`' SCHEMA '`
`' USER '`
| `, objectNameString`
`' CONSTANT '`
`' CONSTRAINT '`
`' DOMAIN '`
`' INDEX '`
`' ROUTINE '`
`' SEQUENCE '`
`' SYNONYM '`
`' TABLE '`
`' TRIGGER '`
| `, schemaNameString , objectNameString`
| `)`
Returns internal identifier of the specified database object as integer value or `NULL` if object doesn't exist. Admin rights are required to execute this function. Example: CALL DB_OBJECT_ID('ROLE', 'MANAGER'); CALL DB_OBJECT_ID('TABLE', 'PUBLIC', 'MY_TABLE'); ### DB_OBJECT_SQL
    DB_OBJECT_SQL({{'ROLE'|'SETTING'|'SCHEMA'|'USER'}, objectNameString
    | {'CONSTANT'|'CONSTRAINT'|'DOMAIN'|'INDEX'|'ROUTINE'|'SEQUENCE'
        |'SYNONYM'|'TABLE'|'TRIGGER'}, schemaNameString, objectNameString })
`DB_OBJECT_SQL (` |
`' ROLE '`
`' SETTING '`
`' SCHEMA '`
`' USER '`
| `, objectNameString`
`' CONSTANT '`
`' CONSTRAINT '`
`' DOMAIN '`
`' INDEX '`
`' ROUTINE '`
`' SEQUENCE '`
`' SYNONYM '`
`' TABLE '`
`' TRIGGER '`
| `, schemaNameString , objectNameString`
| `)`
Returns internal SQL definition of the specified database object or `NULL` if object doesn't exist or it is a system object without SQL definition. This function should not be used to analyze structure of the object by machine code. Internal SQL representation may contain undocumented non-standard clauses and may be different in different versions of H2. Objects are described in the `INFORMATION_SCHEMA` in machine-readable way. Admin rights are required to execute this function. Example: CALL DB_OBJECT_SQL('ROLE', 'MANAGER'); CALL DB_OBJECT_SQL('TABLE', 'PUBLIC', 'MY_TABLE'); ### DECODE
    DECODE(value, whenValue, thenValue [,...])
`DECODE ( value , whenValue , thenValue` |
| `, ...` |
| `)`
Returns the first matching value. `NULL` is considered to match `NULL`. If no match was found, then `NULL` or the last parameter (if the parameter count is even) is returned. This function is provided for Oracle compatibility, use `CASE` instead of it. Example: CALL DECODE(RAND()>0.5, 0, 'Red', 1, 'Black'); ### DISK_SPACE_USED
    DISK_SPACE_USED(tableNameString)
`DISK_SPACE_USED ( tableNameString )`
Returns the approximate amount of space used by the table specified. Does not currently take into account indexes or `LOB`'s. This function may be expensive since it has to load every page in the table. Example: CALL DISK_SPACE_USED('my_table'); ### SIGNAL
    SIGNAL(sqlStateString, messageString)
`SIGNAL ( sqlStateString , messageString )`
Throw an `SQLException` with the passed `SQLState` and reason. Example: CALL SIGNAL('23505', 'Duplicate user ID: ' || user_id); ### ESTIMATED_ENVELOPE
    ESTIMATED_ENVELOPE(tableNameString, columnNameString)
`ESTIMATED_ENVELOPE ( tableNameString , columnNameString )`
Returns the estimated minimum bounding box that encloses all specified `GEOMETRY` values. Only 2D coordinate plane is supported. `NULL` values are ignored. Column must have a spatial index. This function is fast, but estimation may include uncommitted data (including data from other transactions), may return approximate bounds, or be different with actual value due to other reasons. Use with caution. If estimation is not available this function returns `NULL`. For accurate and reliable result use `ESTIMATE` aggregate function instead. Example: CALL ESTIMATED_ENVELOPE('MY_TABLE', 'GEOMETRY_COLUMN'); ### FILE_READ
    FILE_READ(fileNameString [,encodingString])
`FILE_READ ( fileNameString` |
`, encodingString`
| `)`
Returns the contents of a file. If only one parameter is supplied, the data are returned as a `BLOB`. If two parameters are used, the data is returned as a `CLOB` (text). The second parameter is the character set to use, `NULL` meaning the default character set for this system. File names and URLs are supported. To read a stream from the classpath, use the prefix `classpath:`. Admin rights are required to execute this command. Example: SELECT LENGTH(FILE_READ('~/.h2.server.properties')) LEN; SELECT FILE_READ('http://localhost:8182/stylesheet.css', NULL) CSS; ### FILE_WRITE
    FILE_WRITE(blobValue, fileNameString)
`FILE_WRITE ( blobValue , fileNameString )`
Write the supplied parameter into a file. Return the number of bytes written. Write access to folder, and admin rights are required to execute this command. Example: SELECT FILE_WRITE('Hello world', '/tmp/hello.txt')) LEN; ### GREATEST
    GREATEST(aValue, bValue [,...])
`GREATEST ( aValue , bValue` |
| `, ...` |
| `)`
Returns the largest value that is not `NULL`, or `NULL` if all values are `NULL`. Example: CALL GREATEST(1, 2, 3); ### LEAST
    LEAST(aValue, bValue [,...])
`LEAST ( aValue , bValue` |
| `, ...` |
| `)`
Returns the smallest value that is not `NULL`, or `NULL` if all values are `NULL`. Example: CALL LEAST(1, 2, 3); ### LOCK_MODE
    LOCK_MODE()
`LOCK_MODE ( )`
Returns the current lock mode. See `SET LOCK_MODE`. This method returns an int. Example: CALL LOCK_MODE(); ### LOCK_TIMEOUT
    LOCK_TIMEOUT()
`LOCK_TIMEOUT ( )`
Returns the lock timeout of the current session (in milliseconds). Example: LOCK_TIMEOUT() ### MEMORY_FREE
    MEMORY_FREE()
`MEMORY_FREE ( )`
Returns the free memory in KB (where 1024 bytes is a KB). This method returns a long. The garbage is run before returning the value. Admin rights are required to execute this command. Example: MEMORY_FREE() ### MEMORY_USED
    MEMORY_USED()
`MEMORY_USED ( )`
Returns the used memory in KB (where 1024 bytes is a KB). This method returns a long. The garbage is run before returning the value. Admin rights are required to execute this command. Example: MEMORY_USED() ### NEXTVAL
    NEXTVAL ( [ schemaNameString, ] sequenceString )
`NEXTVAL (` |
`schemaNameString ,`
| `sequenceString )`
Increments the sequence and returns its value. The current value of the sequence and the last identity in the current session are updated with the generated value. Used values are never re-used, even when the transaction is rolled back. This method exists only for compatibility, it's recommended to use the standard `NEXT VALUE FOR` sequenceName instead. If the schema name is not set, the current schema is used. When sequence is not found, the uppercase name is also checked. This method returns a long. Example: NEXTVAL('TEST_SEQ') ### NULLIF
    NULLIF(aValue, bValue)
`NULLIF ( aValue , bValue )`
Returns `NULL` if 'a' is equal to 'b', otherwise 'a'. Example: NULLIF(A, B) A / NULLIF(B, 0) ### NVL2
    NVL2(testValue, aValue, bValue)
`NVL2 ( testValue , aValue , bValue )`
If the test value is null, then 'b' is returned. Otherwise, 'a' is returned. The data type of the returned value is the data type of 'a' if this is a text type. This function is provided for Oracle compatibility, use `CASE` or `COALESCE` instead of it. Example: NVL2(X, 'not null', 'null') ### READONLY
    READONLY()
`READONLY ( )`
Returns true if the database is read-only. Example: READONLY() ### ROWNUM
    ROWNUM()
`ROWNUM ( )`
Returns the number of the current row. This method returns a long value. It is supported for `SELECT` statements, as well as for `DELETE` and `UPDATE`. The first row has the row number 1, and is calculated before ordering and grouping the result set, but after evaluating index conditions (even when the index conditions are specified in an outer query). Use the `ROW_NUMBER`() `OVER` () function to get row numbers after grouping or in specified order. Example: SELECT ROWNUM(), * FROM TEST; SELECT ROWNUM(), * FROM (SELECT * FROM TEST ORDER BY NAME); SELECT ID FROM (SELECT T.*, ROWNUM AS R FROM TEST T) WHERE R BETWEEN 2 AND 3; ### SESSION_ID
    SESSION_ID()
`SESSION_ID ( )`
Returns the unique session id number for the current database connection. This id stays the same while the connection is open. This method returns an int. The database engine may re-use a session id after the connection is closed. Example: CALL SESSION_ID() ### SET
    SET(@variableName, value)
`SET ( @variableName , value )`
Updates a variable with the given value. The new value is returned. When used in a query, the value is updated in the order the rows are read. When used in a subquery, not all rows might be read depending on the query plan. This can be used to implement running totals / cumulative sums. Example: SELECT X, SET(@I, COALESCE(@I, 0)+X) RUNNING_TOTAL FROM SYSTEM_RANGE(1, 10) ### TRANSACTION_ID
    TRANSACTION_ID()
`TRANSACTION_ID ( )`
Returns the current transaction id for this session. This method returns `NULL` if there is no uncommitted change, or if the database is not persisted. Otherwise a value of the following form is returned: `logFileId-position-sessionId`. This method returns a string. The value is unique across database restarts (values are not re-used). Example: CALL TRANSACTION_ID() ### TRUNCATE_VALUE
    TRUNCATE_VALUE(value, precisionInt, forceBoolean)
`TRUNCATE_VALUE ( value , precisionInt , forceBoolean )`
Truncate a value to the required precision. If force flag is set to `FALSE` fixed precision values are not truncated. The method returns a value with the same data type as the first parameter. Example: CALL TRUNCATE_VALUE(X, 10, TRUE); ### CURRENT_PATH
    CURRENT_PATH
`CURRENT_PATH` Returns the comma-separated list of quoted schema names where user-defined functions are searched when they are referenced without the schema name. Example: CURRENT_PATH ### CURRENT_ROLE
    CURRENT_ROLE
`CURRENT_ROLE` Returns the name of the `PUBLIC` role. Example: CURRENT_ROLE ### CURRENT_USER
    CURRENT_USER | SESSION_USER | SYSTEM_USER | USER
| `CURRENT_USER` |
| `SESSION_USER` |
| `SYSTEM_USER` |
| `USER` |
Returns the name of the current user of this session. Example: CURRENT_USER ### H2VERSION
    H2VERSION()
`H2VERSION ( )`
Returns the H2 version as a String. Example: H2VERSION() ## JSON Functions ### JSON_OBJECT
    JSON_OBJECT(
    [{{[KEY] string VALUE expression} | {string : expression}} [,...] ]
    [ { NULL | ABSENT } ON NULL ]
    [ { WITH | WITHOUT } UNIQUE KEYS ]
    )
`JSON_OBJECT (`
| `KEY` |
| `string VALUE expression`
`string : expression`
| `, ...` |
| `NULL` |
| `ABSENT` |
| `ON NULL`
| `WITH` |
| `WITHOUT` |
| `UNIQUE KEYS`
`)` Returns a `JSON` object constructed from the specified properties. If `ABSENT ON NULL` is specified properties with `NULL` value are not included in the object. If `WITH UNIQUE KEYS` is specified the constructed object is checked for uniqueness of keys, nested objects, if any, are checked too. Example: JSON_OBJECT('id': 100, 'name': 'Joe', 'groups': '[2,5]' FORMAT JSON); ### JSON_ARRAY
    JSON_ARRAY(
    [expression [,...]]|{(query) [FORMAT JSON]}
    [ { NULL | ABSENT } ON NULL ]
    )
`JSON_ARRAY (`
`expression` |
| `, ...` |
`( query )` |
`FORMAT JSON`
| `NULL` |
| `ABSENT` |
| `ON NULL`
`)` Returns a `JSON` array constructed from the specified values or from the specified single-column subquery. If `NULL ON NULL` is specified `NULL` values are included in the array. Example: JSON_ARRAY(10, 15, 20); JSON_ARRAY(JSON_DATA_A FORMAT JSON, JSON_DATA_B FORMAT JSON); JSON_ARRAY((SELECT J FROM PROPS) FORMAT JSON); ## Table Functions ### CSVREAD
    CSVREAD(fileNameString [, columnsString [, csvOptions ] ] )
`CSVREAD ( fileNameString` |
`, columnsString` |
`, csvOptions`
| `)`
Returns the result set of reading the `CSV` (comma separated values) file. For each parameter, `NULL` means the default value should be used. If the column names are specified (a list of column names separated with the fieldSeparator), those are used, otherwise (or if they are set to `NULL`) the first line of the file is interpreted as the column names. In that case, column names that contain no special characters (only letters, '_', and digits; similar to the rule for Java identifiers) are processed is the same way as unquoted identifiers and therefore case of characters may be changed. Other column names are processed as quoted identifiers and case of characters is preserved. To preserve the case of column names unconditionally use caseSensitiveColumnNames option. The default charset is the default value for this system, and the default field separator is a comma. Missing unquoted values as well as data that matches nullString is parsed as `NULL`. All columns are of type `VARCHAR`. The `BOM` (the byte-order-mark) character 0xfeff at the beginning of the file is ignored. This function can be used like a table: `SELECT * FROM CSVREAD(...)`. Instead of a file, a URL may be used, for example `jar:file:///c:/temp/example.zip!/org/example/nested.csv`. To read a stream from the classpath, use the prefix `classpath:`. To read from `HTTP`, use the prefix `http:` (as in a browser). For performance reason, `CSVREAD` should not be used inside a join. Instead, import the data first (possibly into a temporary table) and then use the table. Admin rights are required to execute this command. Example: SELECT * FROM CSVREAD('test.csv'); -- Read a file containing the columns ID, NAME with SELECT * FROM CSVREAD('test2.csv', 'ID|NAME', 'charset=UTF-8 fieldSeparator=|'); SELECT * FROM CSVREAD('data/test.csv', null, 'rowSeparator=;'); -- Read a tab-separated file SELECT * FROM CSVREAD('data/test.tsv', null, 'rowSeparator=' || CHAR(9)); SELECT "Last Name" FROM CSVREAD('address.csv'); SELECT "Last Name" FROM CSVREAD('classpath:/org/acme/data/address.csv'); ### LINK_SCHEMA
    LINK_SCHEMA (targetSchemaString, driverString, urlString,
    userString, passwordString, sourceSchemaString)
`LINK_SCHEMA ( targetSchemaString , driverString , urlString ,`
`userString , passwordString , sourceSchemaString )`
Creates table links for all tables in a schema. If tables with the same name already exist, they are dropped first. The target schema is created automatically if it does not yet exist. The driver name may be empty if the driver is already loaded. The list of tables linked is returned in the form of a result set. Admin rights are required to execute this command. Example: SELECT * FROM LINK_SCHEMA('TEST2', '', 'jdbc:h2:./test2', 'sa', 'sa', 'PUBLIC'); ### TABLE
    { TABLE | TABLE_DISTINCT }
    ( { name dataTypeOrDomain = {array|rowValueExpression} } [,...] )
| `TABLE` |
| `TABLE_DISTINCT` |
`( name dataTypeOrDomain =` |
| `array` |
| `rowValueExpression` |
| `, ...` |
| `)`
Returns the result set. `TABLE_DISTINCT` removes duplicate rows. Example: SELECT * FROM TABLE(V INT = ARRAY[1, 2]); SELECT * FROM TABLE(ID INT=(1, 2), NAME VARCHAR=('Hello', 'World')); ### UNNEST
    UNNEST(arrayExpression, [,...]) [WITH ORDINALITY]
`UNNEST ( arrayExpression ,` |
| `, ...` |
| `)` |
`WITH ORDINALITY`
Returns the result set. Number of columns is equal to number of arguments, plus one additional column with row number if `WITH ORDINALITY` is specified. Number of rows is equal to length of longest specified array. If multiple arguments are specified and they have different length, cells with missing values will contain null values. Example: SELECT * FROM UNNEST(ARRAY['a', 'b', 'c']); SELECT * FROM UNNEST(JSON '["a", "b", "c"]');