▲
Search:
| Highlight keyword(s)
Home Download Cheat Sheet Documentation Quickstart Installation Tutorial Features Performance Advanced Reference Commands Functions Data Types SQL Grammar System Tables Javadoc PDF (1.5 MB) Support FAQ Error Analyzer Google Group (English) Google Group (Japanese) Google Group (Chinese) Appendix History & Roadmap License Build Links MVStore Architecture |  | # Functions ## Index ### Aggregate Functions
AVG BIT_AND BIT_OR EVERY ANY COUNT LISTAGG ARRAY_AGG MAX | MIN SUM SELECTIVITY STDDEV_POP STDDEV_SAMP VAR_POP VAR_SAMP RANK aggregate DENSE_RANK aggregate | PERCENT_RANK aggregate CUME_DIST aggregate PERCENTILE_CONT PERCENTILE_DISC MEDIAN MODE ENVELOPE
### Numeric Functions
ABS ACOS ASIN ATAN COS COSH COT SIN SINH TAN TANH ATAN2 BITAND BITGET | BITOR BITXOR MOD CEILING DEGREES EXP FLOOR LN LOG10 ORA_HASH RADIANS SQRT PI POWER | RAND RANDOM_UUID ROUND ROUNDMAGIC SECURE_RAND SIGN ENCRYPT DECRYPT HASH TRUNCATE COMPRESS EXPAND ZERO
### String Functions
ASCII BIT_LENGTH LENGTH OCTET_LENGTH CHAR CONCAT CONCAT_WS DIFFERENCE HEXTORAW RAWTOHEX INSTR INSERT Function LOWER UPPER | LEFT RIGHT LOCATE POSITION LPAD RPAD LTRIM RTRIM TRIM REGEXP_REPLACE REGEXP_LIKE REPEAT REPLACE SOUNDEX | SPACE STRINGDECODE STRINGENCODE STRINGTOUTF8 SUBSTRING UTF8TOSTRING XMLATTR XMLNODE XMLCOMMENT XMLCDATA XMLSTARTDOC XMLTEXT TO_CHAR TRANSLATE
### Time and Date Functions
CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP LOCALTIME LOCALTIMESTAMP DATEADD DATEDIFF 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
ARRAY_GET ARRAY_LENGTH ARRAY_CONTAINS ARRAY_CAT ARRAY_APPEND ARRAY_SLICE AUTOCOMMIT CANCEL_SESSION CASEWHEN Function CAST COALESCE CONVERT CURRVAL CSVREAD CSVWRITE DATABASE | DATABASE_PATH DECODE DISK_SPACE_USED SIGNAL ESTIMATED_ENVELOPE FILE_READ FILE_WRITE GREATEST IDENTITY IFNULL LEAST LOCK_MODE LOCK_TIMEOUT LINK_SCHEMA MEMORY_FREE MEMORY_USED | NEXTVAL NULLIF NVL2 READONLY ROWNUM SCHEMA SCOPE_IDENTITY SESSION_ID SET TABLE TRANSACTION_ID TRUNCATE_VALUE UNNEST USER H2VERSION
### Window Functions
ROW_NUMBER RANK DENSE_RANK PERCENT_RANK | CUME_DIST NTILE LEAD LAG | FIRST_VALUE LAST_VALUE NTH_VALUE RATIO_TO_REPORT
## Details Click on the header to switch between railroad diagram and BNF. ## Aggregate Functions ### AVG
    AVG ( [ DISTINCT|ALL ] { numeric } )
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`AVG (` |
| `DISTINCT` |
| `ALL` |
| `numeric )`
`FILTER ( WHERE expression )`
`OVER windowNameOrSpecification`
The average (mean) value. If no rows are selected, the result is `NULL`. Aggregates are only allowed in select statements. The returned value is of the same data type as the parameter. Example: AVG(X) ### BIT_AND
    BIT_AND(expression)
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`BIT_AND ( expression )`
`FILTER ( WHERE expression )`
`OVER windowNameOrSpecification`
The bitwise `AND` of all non-null values. If no rows are selected, the result is `NULL`. Aggregates are only allowed in select statements. Example: BIT_AND(ID) ### BIT_OR
    BIT_OR(expression)
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`BIT_OR ( expression )`
`FILTER ( WHERE expression )`
`OVER windowNameOrSpecification`
The bitwise `OR` of all non-null values. If no rows are selected, the result is `NULL`. Aggregates are only allowed in select statements. Example: BIT_OR(ID) ### EVERY
    {EVERY|BOOL_AND}(boolean)
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
| `EVERY` |
| `BOOL_AND` |
| `( boolean )`
`FILTER ( WHERE expression )`
`OVER windowNameOrSpecification`
Returns true if all expressions are true. If no rows are selected, the result is `NULL`. Aggregates are only allowed in select statements. Example: EVERY(ID>10) ### ANY
    {ANY|SOME|BOOL_OR}(boolean)
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
| `ANY` |
| `SOME` |
| `BOOL_OR` |
| `( boolean )`
`FILTER ( WHERE expression )`
`OVER windowNameOrSpecification`
Returns true if any expression is true. If no rows are selected, the result is `NULL`. Aggregates are only allowed in select statements. Note that if `ANY` or `SOME` aggregate function is placed on the right side of comparison operation and argument of this function is a subquery additional parentheses around aggregate function are required, otherwise it will be parsed as quantified comparison predicate. Example: ANY(NAME LIKE 'W%') A = (ANY((SELECT B FROM T))) ### COUNT
    COUNT( { * | { [ DISTINCT|ALL ] expression } } )
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`COUNT (` |
| `*` |
| `DISTINCT` |
| `ALL` |
| `expression`
| `)`
`FILTER ( WHERE expression )`
`OVER windowNameOrSpecification`
The count of all row, or of the non-null values. This method returns a long. If no rows are selected, the result is 0. Aggregates are only allowed in select statements. Example: COUNT(*) ### LISTAGG
    { LISTAGG ( [ DISTINCT|ALL ] string [, separatorString] [ ON OVERFLOW ERROR ] )
    withinGroupSpecification }
    | { GROUP_CONCAT ( [ DISTINCT|ALL ] string
    [ ORDER BY { expression [ ASC | DESC ] } [,...] ]
    [ SEPARATOR separatorString ] ) }
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`LISTAGG (` |
| `DISTINCT` |
| `ALL` |
| `string` |
`, separatorString`
`ON OVERFLOW ERROR`
| `) withinGroupSpecification`
`| GROUP_CONCAT (` |
| `DISTINCT` |
| `ALL` |
| `string` |
`ORDER BY expression` |
| `ASC` |
| `DESC` |
| `, ...` |
`SEPARATOR separatorString`
| `)`
`FILTER ( WHERE expression )`
`OVER windowNameOrSpecification`
Concatenates strings with a separator. Separator must be the same for all rows in the same group. The default separator is a ',' (without space). This method returns a string. If no rows are selected, the result is `NULL`. Aggregates are only allowed in select statements. Example: LISTAGG(NAME, ', ') WITHIN GROUP (ORDER BY ID) LISTAGG(ID, ', ') WITHIN GROUP (ORDER BY ID) OVER (ORDER BY ID) ### ARRAY_AGG
    ARRAY_AGG ( [ DISTINCT|ALL ] string
    [ ORDER BY { expression [ ASC | DESC ] } [,...] ] )
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`ARRAY_AGG (` |
| `DISTINCT` |
| `ALL` |
| `string`
`ORDER BY expression` |
| `ASC` |
| `DESC` |
| `, ...` |
| `)`
`FILTER ( WHERE expression )`
`OVER windowNameOrSpecification`
Aggregate the value into an array. This method returns an array. If no rows are selected, the result is `NULL`. If `ORDER BY` is not specified order of values is not determined. When this aggregate is used with `OVER` clause that contains `ORDER BY` subclause it does not enforce exact order of values. This aggregate needs additional own `ORDER BY` clause to make it deterministic. Aggregates are only allowed in select statements. Example: ARRAY_AGG(NAME ORDER BY ID) ARRAY_AGG(ID ORDER BY ID) OVER (ORDER BY ID) ### MAX
    MAX(value)
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`MAX ( value )`
`FILTER ( WHERE expression )`
`OVER windowNameOrSpecification`
The highest value. If no rows are selected, the result is `NULL`. Aggregates are only allowed in select statements. The returned value is of the same data type as the parameter. Example: MAX(NAME) ### MIN
    MIN(value)
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`MIN ( value )`
`FILTER ( WHERE expression )`
`OVER windowNameOrSpecification`
The lowest value. If no rows are selected, the result is `NULL`. Aggregates are only allowed in select statements. The returned value is of the same data type as the parameter. Example: MIN(NAME) ### SUM
    SUM( [ DISTINCT|ALL ] { numeric } )
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`SUM (` |
| `DISTINCT` |
| `ALL` |
| `numeric )`
`FILTER ( WHERE expression )`
`OVER windowNameOrSpecification`
The sum of all values. If no rows are selected, the result is `NULL`. Aggregates are only allowed in select statements. The data type of the returned value depends on the parameter data type like this: `BOOLEAN, TINYINT, SMALLINT, INT -> BIGINT, BIGINT -> DECIMAL, REAL -> DOUBLE` Example: SUM(X) ### SELECTIVITY
    SELECTIVITY(value)
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`SELECTIVITY ( value )`
`FILTER ( WHERE expression )`
`OVER windowNameOrSpecification`
Estimates the selectivity (0-100) of a value. The value is defined as (100 * distinctCount / rowCount). The selectivity of 0 rows is 0 (unknown). Up to 10000 values are kept in memory. Aggregates are only allowed in select statements. Example: SELECT SELECTIVITY(FIRSTNAME), SELECTIVITY(NAME) FROM TEST WHERE ROWNUM()<20000 ### STDDEV_POP
    STDDEV_POP( [ DISTINCT|ALL ] numeric )
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`STDDEV_POP (` |
| `DISTINCT` |
| `ALL` |
| `numeric )`
`FILTER ( WHERE expression )`
`OVER windowNameOrSpecification`
The population standard deviation. This method returns a double. If no rows are selected, the result is `NULL`. Aggregates are only allowed in select statements. Example: STDDEV_POP(X) ### STDDEV_SAMP
    STDDEV_SAMP( [ DISTINCT|ALL ] numeric )
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`STDDEV_SAMP (` |
| `DISTINCT` |
| `ALL` |
| `numeric )`
`FILTER ( WHERE expression )`
`OVER windowNameOrSpecification`
The sample standard deviation. This method returns a double. If no rows are selected, the result is `NULL`. Aggregates are only allowed in select statements. Example: STDDEV(X) ### VAR_POP
    VAR_POP( [ DISTINCT|ALL ] numeric )
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`VAR_POP (` |
| `DISTINCT` |
| `ALL` |
| `numeric )`
`FILTER ( WHERE expression )`
`OVER windowNameOrSpecification`
The population variance (square of the population standard deviation). This method returns a double. If no rows are selected, the result is `NULL`. Aggregates are only allowed in select statements. Example: VAR_POP(X) ### VAR_SAMP
    VAR_SAMP( [ DISTINCT|ALL ] numeric )
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`VAR_SAMP (` |
| `DISTINCT` |
| `ALL` |
| `numeric )`
`FILTER ( WHERE expression )`
`OVER windowNameOrSpecification`
The sample variance (square of the sample standard deviation). This method returns a double. If no rows are selected, the result is `NULL`. Aggregates are only allowed in select statements. Example: VAR_SAMP(X) ### RANK aggregate
    RANK(value [,...])
    withinGroupSpecification
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`RANK ( value` |
| `, ...` |
| `)`
`withinGroupSpecification`
`FILTER ( WHERE expression )`
`OVER windowNameOrSpecification`
Returns the rank of the hypothetical row in specified collection of rows. The rank of a row is the number of rows that precede this row plus 1. If two or more rows have the same values in `ORDER BY` columns, these rows get the same rank from the first row with the same values. It means that gaps in ranks are possible. Example: SELECT RANK(5) WITHIN GROUP (ORDER BY V) FROM TEST; ### DENSE_RANK aggregate
    DENSE_RANK(value [,...])
    withinGroupSpecification
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`DENSE_RANK ( value` |
| `, ...` |
| `)`
`withinGroupSpecification`
`FILTER ( WHERE expression )`
`OVER windowNameOrSpecification`
Returns the dense rank of the hypothetical row in specified collection of rows. The rank of a row is the number of groups of rows with the same values in `ORDER BY` columns that precede group with this row plus 1. If two or more rows have the same values in `ORDER BY` columns, these rows get the same rank. Gaps in ranks are not possible. Example: SELECT DENSE_RANK(5) WITHIN GROUP (ORDER BY V) FROM TEST; ### PERCENT_RANK aggregate
    PERCENT_RANK(value [,...])
    withinGroupSpecification
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`PERCENT_RANK ( value` |
| `, ...` |
| `)`
`withinGroupSpecification`
`FILTER ( WHERE expression )`
`OVER windowNameOrSpecification`
Returns the relative rank of the hypothetical row in specified collection of rows. The relative rank is calculated as (`RANK` - 1) / (`NR` - 1), where `RANK` is a rank of the row and `NR` is a total number of rows in the collection including hypothetical row. Example: SELECT PERCENT_RANK(5) WITHIN GROUP (ORDER BY V) FROM TEST; ### CUME_DIST aggregate
    CUME_DIST(value [,...])
    withinGroupSpecification
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`CUME_DIST ( value` |
| `, ...` |
| `)`
`withinGroupSpecification`
`FILTER ( WHERE expression )`
`OVER windowNameOrSpecification`
Returns the relative rank of the hypothetical row in specified collection of rows. The relative rank is calculated as `NP` / `NR` where `NP` is a number of rows that precede the current row or have the same values in `ORDER BY` columns and `NR` is a total number of rows in the collection including hypothetical row. Example: SELECT CUME_DIST(5) WITHIN GROUP (ORDER BY V) FROM TEST; ### PERCENTILE_CONT
    PERCENTILE_CONT(numeric) WITHIN GROUP (ORDER BY value [ASC|DESC])
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`PERCENTILE_CONT ( numeric ) WITHIN GROUP ( ORDER BY value` |
| `ASC` |
| `DESC` |
| `)`
`FILTER ( WHERE expression )`
`OVER windowNameOrSpecification`
Return percentile of values from the group with interpolation. Interpolation is only supported for numeric, date-time, and interval data types. Argument must be between 0 and 1 inclusive. Argument must be the same for all rows in the same group. If argument is `NULL`, the result is `NULL`. `NULL` values are ignored in the calculation. If no rows are selected, the result is `NULL`. Aggregates are only allowed in select statements. Example: PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY V) ### PERCENTILE_DISC
    PERCENTILE_DISC(numeric) WITHIN GROUP (ORDER BY value [ASC|DESC])
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`PERCENTILE_DISC ( numeric ) WITHIN GROUP ( ORDER BY value` |
| `ASC` |
| `DESC` |
| `)`
`FILTER ( WHERE expression )`
`OVER windowNameOrSpecification`
Return percentile of values from the group. Interpolation is not performed. Argument must be between 0 and 1 inclusive. Argument must be the same for all rows in the same group. If argument is `NULL`, the result is `NULL`. `NULL` values are ignored in the calculation. If no rows are selected, the result is `NULL`. Aggregates are only allowed in select statements. Example: PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY V) ### MEDIAN
    MEDIAN( [ DISTINCT|ALL ] value )
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`MEDIAN (` |
| `DISTINCT` |
| `ALL` |
| `value )`
`FILTER ( WHERE expression )`
`OVER windowNameOrSpecification`
The value separating the higher half of a values from the lower half. Returns the middle value or an interpolated value between two middle values if number of values is even. Interpolation is only supported for numeric, date-time, and interval data types. `NULL` values are ignored in the calculation. If no rows are selected, the result is `NULL`. Aggregates are only allowed in select statements. Example: MEDIAN(X) ### MODE
    { MODE( value ) [ ORDER BY expression [ ASC | DESC ] ] }
    | { MODE() WITHIN GROUP (ORDER BY expression [ ASC | DESC ]) }
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`MODE ( value )` |
`ORDER BY expression` |
| `ASC` |
| `DESC` |
`MODE ( ) WITHIN GROUP ( ORDER BY expression` |
| `ASC` |
| `DESC` |
| `)`
`FILTER ( WHERE expression )`
`OVER windowNameOrSpecification`
Returns the value that occurs with the greatest frequency. If there are multiple values with the same frequency only one value will be returned. In this situation value will be chosen based on optional `ORDER BY` clause that should specify exactly the same expression as argument of this function. Use ascending order to get smallest value or descending order to get largest value from multiple values with the same frequency. If this clause is not specified the exact chosen value is not determined in this situation. `NULL` values are ignored in the calculation. If no rows are selected, the result is `NULL`. Aggregates are only allowed in select statements. Example: MODE(X) MODE(X ORDER BY X) MODE() WITHIN GROUP (ORDER BY X) ### ENVELOPE
    ENVELOPE( value )
    [FILTER (WHERE expression)] [OVER windowNameOrSpecification]
`ENVELOPE ( value )`
`FILTER ( WHERE expression )`
`OVER windowNameOrSpecification`
Returns the minimum bounding box that encloses all specified `GEOMETRY` values. Only 2D coordinate plane is supported. `NULL` values are ignored in the calculation. If no rows are selected, the result is `NULL`. Aggregates are only allowed in select statements. Example: ENVELOPE(X) ## 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(VALUE) ABS(CAST(VALUE 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(long, long)
`BITAND ( long , long )`
The bitwise `AND` operation. This method returns a long. See also Java operator &. Example: BITAND(A, B) ### BITGET
    BITGET(long, int)
`BITGET ( long , int )`
Returns true if and only if the first parameter has a bit set in the position specified by the second parameter. This method returns a boolean. The second parameter is zero-indexed; the least significant bit has position 0. Example: BITGET(A, 1) ### BITOR
    BITOR(long, long)
`BITOR ( long , long )`
The bitwise `OR` operation. This method returns a long. See also Java operator |. Example: BITOR(A, B) ### BITXOR
    BITXOR(long, long)
`BITXOR ( long , long )`
The bitwise `XOR` operation. This method returns a long. See also Java operator ^. Example: BITXOR(A, B) ### MOD
    MOD(long, long)
`MOD ( long , long )`
The modulo operation. This method returns a long. See also Java operator %. Example: MOD(A, B) ### CEILING
    { CEILING | CEIL } (numeric)
| `CEILING` |
| `CEIL` |
| `( numeric )`
See also Java `Math.ceil`. This method returns a double. 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 )`
See also Java `Math.floor`. This method returns a double. Example: FLOOR(A) ### LN
    {LN|LOG}(numeric)
| `LN` |
| `LOG` |
| `( numeric )`
Calculates the natural (base e) logarithm as a double value. In the PostgreSQL mode, `LOG`(x) is base 10. See also Java `Math.log`. Example: LN(A) ### LOG10
    LOG10(numeric)
`LOG10 ( numeric )`
Calculates the base 10 logarithm as a double value. See also Java `Math.log10`. 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 digits, or to the nearest long if the number of digits if not set. This method returns a numeric (the same type as the input). Example: ROUND(VALUE, 2) ### ROUNDMAGIC
    ROUNDMAGIC(numeric)
`ROUNDMAGIC ( numeric )`
This function rounds numbers in a good way, but it is slow. It has a special handling for numbers around 0. Only numbers smaller or equal +/-1000000000000 are supported. The value is converted to a String internally, and then the last 4 characters are checked. '000x' becomes '0000' and '999x' becomes '999999', which is rounded automatically. This method returns a double. Example: ROUNDMAGIC(VALUE/3*3) ### 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, and otherwise 1. Example: SIGN(VALUE) ### 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. Currently, the only algorithm supported is `SHA256`. This method returns bytes. Example: CALL HASH('SHA256', STRINGTOUTF8('Password'), 1000) ### TRUNCATE
    { TRUNC | TRUNCATE } ( { {numeric, digitsInt}
    | timestamp | timestampWithTimeZone | date | timestampString } )
| `TRUNC` |
| `TRUNCATE` |
| `(` |
`numeric , digitsInt`
| `timestamp` |
| `timestampWithTimeZone` |
| `date` |
| `timestampString` |
| `)`
Truncates to a number of digits (to the next value closer to 0). This method returns a double. When used with a timestamp, truncates a timestamp to a date (day) value. When used with a date, truncates a date to a date (day) value less time part. When used with a timestamp as string, truncates a timestamp to a date (day) value. Example: TRUNCATE(VALUE, 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(string)
`BIT_LENGTH ( string )`
Returns the number of bits in a string. This method returns a long. For `BLOB, CLOB, BYTES` and `JAVA_OBJECT`, the precision is used. Each character needs 16 bits. Example: BIT_LENGTH(NAME) ### LENGTH
    { LENGTH | CHAR_LENGTH | CHARACTER_LENGTH } ( string )
| `LENGTH` |
| `CHAR_LENGTH` |
| `CHARACTER_LENGTH` |
| `( string )`
Returns the number of characters in a string. This method returns a long. For `BLOB, CLOB, BYTES` and `JAVA_OBJECT`, the precision is used. Example: LENGTH(NAME) ### OCTET_LENGTH
    OCTET_LENGTH(string)
`OCTET_LENGTH ( string )`
Returns the number of bytes in a string. This method returns a long. For `BLOB, CLOB, BYTES` and `JAVA_OBJECT`, the precision is used. Each character needs 2 bytes. 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`. This method returns a string. Example: CONCAT(NAME, '!') ### CONCAT_WS
    CONCAT_WS(separatorString, string, string [,...])
`CONCAT_WS ( separatorString , string , string` |
| `, ...` |
| `)`
Combines strings with separator. Unlike with the operator `||, NULL` parameters are ignored, and do not cause the result to become `NULL`. 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)
`RAWTOHEX ( string )`
Converts a string to the hex representation. 4 hex characters per string character are used. This method returns a string. Example: RAWTOHEX(DATA) ### INSTR
    INSTR(string, searchString, [, startInt])
`INSTR ( string , searchString ,` |
`, startInt`
| `)`
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: INSTR(EMAIL,'@') ### 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])
`LOCATE ( searchString , string` |
`, startInt`
| `)`
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. Example: LOCATE('.', NAME) ### POSITION
    POSITION(searchString, string)
`POSITION ( searchString , string )`
Returns the location of a search string in a string. See also `LOCATE`. Example: POSITION('.', 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)
`LTRIM ( string )`
Removes all leading spaces from a string. Example: LTRIM(NAME) ### RTRIM
    RTRIM(string)
`RTRIM ( string )`
Removes all trailing spaces from a string. Example: RTRIM(NAME) ### TRIM
    TRIM ( [ [ LEADING | TRAILING | BOTH ] [ string ] FROM ] string )
`TRIM (` |
| `LEADING` |
| `TRAILING` |
| `BOTH` |
| `string` |
| `FROM`
| `string )`
Removes all leading spaces, trailing spaces, or spaces at both ends, from a string. Other characters can be removed as well. Example: 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 limited to 'i', 'c', 'n', 'm'. Other symbols causes exception. Multiple symbols could be uses in one flagsString parameter (like 'im'). Later flags overrides first ones, for example 'ic' 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 limited to 'i', 'c', 'n', 'm'. Other symbols causes exception. Multiple symbols could be uses in one flagsString parameter (like 'im'). Later flags overrides first ones, for example 'ic' 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') ### 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 code representing the sound of a string. 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 | SUBSTR } ( string, startInt [, lengthInt ] )
| `SUBSTRING` |
| `SUBSTR` |
| `( string , 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. Also supported is: `SUBSTRING(string [FROM start] [FOR length])`. Example: CALL SUBSTR('[Hello]', 2, 5); CALL SUBSTR('Hello World', -5); ### UTF8TOSTRING
    UTF8TOSTRING(bytes)
`UTF8TOSTRING ( bytes )`
Decodes a byte array in the `UTF8` format to a string. Example: CALL UTF8TOSTRING(STRINGTOUTF8('This is a test')) ### 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', 'http://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', 'http://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 | CURDATE() | SYSDATE | TODAY }
| `CURRENT_DATE` |
`CURDATE ( )`
| `SYSDATE` |
| `TODAY` |
Returns the current date. These methods always return the same value within a transaction (default) or within a command depending on database mode. Example: CURRENT_DATE ### CURRENT_TIME
    CURRENT_TIME [ (int) ]
`CURRENT_TIME` |
`( int )`
Returns the current time. The returned value does not have time zone information, because `TIME WITH TIME ZONE` data type is not supported in H2. 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 methods always return the same value within a transaction (default) or within a command depending on database mode. 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 method always returns the same value within a transaction (default) or within a command depending on database mode. Example: CURRENT_TIMESTAMP CURRENT_TIMESTAMP(9) ### LOCALTIME
    { LOCALTIME [ (int) ] | CURTIME([ int ]) }
`LOCALTIME` |
`( int )`
`CURTIME (` |
| `int` |
| `)`
Returns the current time. 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 methods always return the same value within a transaction (default) or within a command depending on database mode. Example: LOCALTIME LOCALTIME(9) ### LOCALTIMESTAMP
    { LOCALTIMESTAMP [ (int) ] | NOW( [ int ] ) }
`LOCALTIMESTAMP` |
`( int )`
`NOW (` |
| `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. These methods always return the same value within a transaction (default) or within a command depending on database mode. 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` and `TIMEZONE_MINUTE` 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` and `TIMEZONE_MINUTE` 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) ### 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). Example: DAY_OF_MONTH(CREATED) ### DAY_OF_WEEK
    DAY_OF_WEEK(dateAndTime)
`DAY_OF_WEEK ( dateAndTime )`
Returns the day of the week (1 means Sunday). 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). 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). 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.text.SimpleDateFormat`. timeZoneString may be specified if dateAndTime is a `DATE, TIME` or `TIMESTAMP`. timeZoneString is ignored if dateAndTime is `TIMESTAMP WITH TIME ZONE`. This method returns a string. 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. Example: HOUR(CREATED) ### MINUTE
    MINUTE(dateAndTime|interval)
`MINUTE ( dateAndTime`
`interval )`
Returns the minute (0-59) from a date/time value. Example: MINUTE(CREATED) ### MONTH
    MONTH(dateAndTime|interval)
`MONTH ( dateAndTime`
`interval )`
Returns the month (1-12) from a date/time value. 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. 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.text.SimpleDateFormat`. 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. Example: QUARTER(CREATED) ### SECOND
    SECOND(dateAndTime)
`SECOND ( dateAndTime )`
Returns the second (0-59) from a date/time value. Example: SECOND(CREATED|interval) ### WEEK
    WEEK(dateAndTime)
`WEEK ( dateAndTime )`
Returns the week (1-53) from a date/time value. This method 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 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. Example: YEAR(CREATED) ### ISO_YEAR
    ISO_YEAR(dateAndTime)
`ISO_YEAR ( dateAndTime )`
Returns the `ISO` week year from a date/time value. Example: ISO_YEAR(CREATED) ## System Functions ### ARRAY_GET
    ARRAY_GET(arrayExpression, indexExpression)
`ARRAY_GET ( arrayExpression , indexExpression )`
Returns element at the specified 1-based index from an array. Returns `NULL` if there is no such element or array is `NULL`. Example: CALL ARRAY_GET(ARRAY['Hello', 'World'], 2) ### ARRAY_LENGTH
    ARRAY_LENGTH(arrayExpression)
`ARRAY_LENGTH ( arrayExpression )`
Returns the length of an array. Returns `NULL` if the specified array is `NULL`. Example: CALL ARRAY_LENGTH(ARRAY['Hello', 'World']) ### 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. 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. Returns `NULL` if any parameter is `NULL`. Example: CALL ARRAY_APPEND(ARRAY[1, 2], 3) ### 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. The method only works if the multithreaded kernel is enabled (see `SET MULTI_THREADED`). 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 'a' if the boolean expression is true, otherwise 'b'. Returns the same data type as the parameter. Example: CASEWHEN(ID=1, 'A', 'B') ### CAST
    CAST(value AS dataType)
`CAST ( value AS dataType )`
Converts a value to another data type. The following conversion rules are used: When converting a number to a boolean, 0 is false and every other value is true. When converting a boolean to a number, false is 0 and true is 1. When converting a number to a number of another type, the value is checked for overflow. When converting a number to binary, the number of bytes matches the precision. When converting a string to binary, it is hex encoded (every byte two characters); a hex string can be converted to a number by first converting it to binary. If a direct conversion is not possible, the value is first converted to a string. Note that some data types may need explicitly specified precision to avoid overflow or rounding. Example: CAST(NAME AS INT); CAST(65535 AS BINARY); CAST(CAST('FFFF' AS BINARY) AS INT); CAST(TIMESTAMP '2010-01-01 10:40:00.123456' AS TIME(6)) ### COALESCE
    { COALESCE | NVL } (aValue, bValue [,...])
| `COALESCE` |
| `NVL` |
| `( aValue , bValue` |
| `, ...` |
| `)`
Returns the first value that is not null. Example: COALESCE(A, B, C) ### CONVERT
    CONVERT(value, dataType)
`CONVERT ( value , dataType )`
Converts a value to another data type. Example: CONVERT(NAME, INT) ### CURRVAL
    CURRVAL( [ schemaName, ] sequenceString )
`CURRVAL (` |
`schemaName ,`
| `sequenceString )`
Returns the current (last) value of the sequence, independent of the session. If the sequence was just created, the method returns (start - interval). If the schema name is not set, the current schema is used. If the schema name is not set, the sequence name is converted to uppercase (for compatibility). This method returns a long. Example: CURRVAL('TEST_SEQ') ### 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 considered case insensitive. Other column names are case sensitive, that means you need to use quoted identifiers (see below). 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 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, an 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: CALL CSVREAD('test.csv'); -- Read a file containing the columns ID, NAME with CALL 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'); ### 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)); ### DATABASE
    DATABASE()
`DATABASE ( )`
Returns the name of the database. Example: CALL DATABASE(); ### 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(); ### 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 (see there for details). 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. This function is only supported by `MVStore` engine. 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); ### IDENTITY
    IDENTITY()
`IDENTITY ( )`
Returns the last inserted identity value for this session. This value changes whenever a new sequence number was generated, even within a trigger or Java function. See also `SCOPE_IDENTITY`. This method returns a long. Example: CALL IDENTITY(); ### IFNULL
    IFNULL(aValue, bValue)
`IFNULL ( aValue , bValue )`
Returns the value of 'a' if it is not null, otherwise 'b'. Example: CALL IFNULL(NULL, ''); ### 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() ### 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: CALL LINK_SCHEMA('TEST2', '', 'jdbc:h2v1_4_199:test2', 'sa', 'sa', 'PUBLIC'); ### MEMORY_FREE
    MEMORY_FREE()
`MEMORY_FREE ( )`
Returns the free memory in KB (where 1024 bytes is a KB). This method returns an int. 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 an int. The garbage is run before returning the value. Admin rights are required to execute this command. Example: MEMORY_USED() ### NEXTVAL
    NEXTVAL ( [ schemaName, ] sequenceString )
`NEXTVAL (` |
`schemaName ,`
| `sequenceString )`
Returns the next value of the sequence. Used values are never re-used, even when the transaction is rolled back. If the schema name is not set, the current schema is used, and the sequence name is converted to uppercase (for compatibility). This method returns a long. Example: NEXTVAL('TEST_SEQ') ### NULLIF
    NULLIF(aValue, bValue)
`NULLIF ( aValue , bValue )`
Returns `NULL` if 'a' is equals to 'b', otherwise 'a'. Example: NULLIF(A, B) ### 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. 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; ### SCHEMA
    SCHEMA()
`SCHEMA ( )`
Returns the name of the default schema for this session. Example: CALL SCHEMA() ### SCOPE_IDENTITY
    SCOPE_IDENTITY()
`SCOPE_IDENTITY ( )`
Returns the last inserted identity value for this session for the current scope (the current statement). Changes within triggers and Java functions are ignored. See also `IDENTITY`(). This method returns a long. Example: CALL SCOPE_IDENTITY(); ### 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, IFNULL(@I, 0)+X) RUNNING_TOTAL FROM SYSTEM_RANGE(1, 10) ### TABLE
    { TABLE | TABLE_DISTINCT }
    ( { name dataType = array|rowValueExpression } [,...] )
| `TABLE` |
| `TABLE_DISTINCT` |
`(` |
`name dataType = array`
| `rowValueExpression` |
| `, ...` |
| `)`
Returns the result set. `TABLE_DISTINCT` removes duplicate rows. Example: SELECT * FROM TABLE(VALUE INT = ARRAY[1, 2]); SELECT * FROM TABLE(ID INT=(1, 2), NAME VARCHAR=('Hello', 'World')); ### 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. The precision of the returned value may be a bit larger than requested, because fixed precision values are not truncated (unlike the numeric `TRUNCATE` method). Unlike `CAST`, the truncating a decimal value may lose precision if the force flag is set to true. The method returns a value with the same data type as the first parameter. Example: CALL TRUNCATE_VALUE(X, 10, TRUE); ### UNNEST
    UNNEST(array, [,...]) [WITH ORDINALITY]
`UNNEST ( array ,` |
| `, ...` |
| `)` |
`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']); ### USER
    { USER | CURRENT_USER } ()
| `USER` |
| `CURRENT_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() ## Window Functions ### ROW_NUMBER
    ROW_NUMBER() OVER windowNameOrSpecification
`ROW_NUMBER ( ) OVER windowNameOrSpecification`
Returns the number of the current row starting with 1. Window frame clause is not allowed for this function. Window functions in H2 may require a lot of memory for large queries. Example: SELECT ROW_NUMBER() OVER (), * FROM TEST; SELECT ROW_NUMBER() OVER (ORDER BY ID), * FROM TEST; SELECT ROW_NUMBER() OVER (PARTITION BY CATEGORY ORDER BY ID), * FROM TEST; ### RANK
    RANK() OVER windowNameOrSpecification
`RANK ( ) OVER windowNameOrSpecification`
Returns the rank of the current row. The rank of a row is the number of rows that precede this row plus 1. If two or more rows have the same values in `ORDER BY` columns, these rows get the same rank from the first row with the same values. It means that gaps in ranks are possible. This function requires window order clause. Window frame clause is not allowed for this function. Window functions in H2 may require a lot of memory for large queries. Example: SELECT RANK() OVER (ORDER BY ID), * FROM TEST; SELECT RANK() OVER (PARTITION BY CATEGORY ORDER BY ID), * FROM TEST; ### DENSE_RANK
    DENSE_RANK() OVER windowNameOrSpecification
`DENSE_RANK ( ) OVER windowNameOrSpecification`
Returns the dense rank of the current row. The rank of a row is the number of groups of rows with the same values in `ORDER BY` columns that precede group with this row plus 1. If two or more rows have the same values in `ORDER BY` columns, these rows get the same rank. Gaps in ranks are not possible. This function requires window order clause. Window frame clause is not allowed for this function. Window functions in H2 may require a lot of memory for large queries. Example: SELECT DENSE_RANK() OVER (ORDER BY ID), * FROM TEST; SELECT DENSE_RANK() OVER (PARTITION BY CATEGORY ORDER BY ID), * FROM TEST; ### PERCENT_RANK
    PERCENT_RANK() OVER windowNameOrSpecification
`PERCENT_RANK ( ) OVER windowNameOrSpecification`
Returns the relative rank of the current row. The relative rank is calculated as (`RANK` - 1) / (`NR` - 1), where `RANK` is a rank of the row and `NR` is a number of rows in window partition with this row. Note that result is always 0 if window order clause is not specified. Window frame clause is not allowed for this function. Window functions in H2 may require a lot of memory for large queries. Example: SELECT PERCENT_RANK() OVER (ORDER BY ID), * FROM TEST; SELECT PERCENT_RANK() OVER (PARTITION BY CATEGORY ORDER BY ID), * FROM TEST; ### CUME_DIST
    CUME_DIST() OVER windowNameOrSpecification
`CUME_DIST ( ) OVER windowNameOrSpecification`
Returns the relative rank of the current row. The relative rank is calculated as `NP` / `NR` where `NP` is a number of rows that precede the current row or have the same values in `ORDER BY` columns and `NR` is a number of rows in window partition with this row. Note that result is always 1 if window order clause is not specified. Window frame clause is not allowed for this function. Window functions in H2 may require a lot of memory for large queries. Example: SELECT CUME_DIST() OVER (ORDER BY ID), * FROM TEST; SELECT CUME_DIST() OVER (PARTITION BY CATEGORY ORDER BY ID), * FROM TEST; ### NTILE
    NTILE(long) OVER windowNameOrSpecification
`NTILE ( long ) OVER windowNameOrSpecification`
Distributes the rows into a specified number of groups. Number of groups should be a positive long value. `NTILE` returns the 1-based number of the group to which the current row belongs. First groups will have more rows if number of rows is not divisible by number of groups. For example, if 5 rows are distributed into 2 groups this function returns 1 for the first 3 row and 2 for the last 2 rows. This function requires window order clause. Window frame clause is not allowed for this function. Window functions in H2 may require a lot of memory for large queries. Example: SELECT NTILE(10) OVER (ORDER BY ID), * FROM TEST; SELECT NTILE(5) OVER (PARTITION BY CATEGORY ORDER BY ID), * FROM TEST; ### LEAD
    LEAD(value [, offsetInt [, defaultValue]]) [{RESPECT|IGNORE} NULLS]
    OVER windowNameOrSpecification
`LEAD ( value` |
`, offsetInt` |
`, defaultValue`
| `)` |
| `RESPECT` |
| `IGNORE` |
| `NULLS`
`OVER windowNameOrSpecification`
Returns the value in a next row with specified offset relative to the current row. Offset must be non-negative. If `IGNORE NULLS` is specified rows with null values in selected expression are skipped. If number of considered rows is less than specified relative number this function returns `NULL` or the specified default value, if any. If offset is 0 the value from the current row is returned unconditionally. This function requires window order clause. Window frame clause is not allowed for this function. Window functions in H2 may require a lot of memory for large queries. Example: SELECT LEAD(X) OVER (ORDER BY ID), * FROM TEST; SELECT LEAD(X, 2, 0) IGNORE NULLS OVER ( PARTITION BY CATEGORY ORDER BY ID ), * FROM TEST; ### LAG
    LAG(value [, offsetInt [, defaultValue]]) [{RESPECT|IGNORE} NULLS]
    OVER windowNameOrSpecification
`LAG ( value` |
`, offsetInt` |
`, defaultValue`
| `)` |
| `RESPECT` |
| `IGNORE` |
| `NULLS`
`OVER windowNameOrSpecification`
Returns the value in a previous row with specified offset relative to the current row. Offset must be non-negative. If `IGNORE NULLS` is specified rows with null values in selected expression are skipped. If number of considered rows is less than specified relative number this function returns `NULL` or the specified default value, if any. If offset is 0 the value from the current row is returned unconditionally. This function requires window order clause. Window frame clause is not allowed for this function. Window functions in H2 may require a lot of memory for large queries. Example: SELECT LAG(X) OVER (ORDER BY ID), * FROM TEST; SELECT LAG(X, 2, 0) IGNORE NULLS OVER ( PARTITION BY CATEGORY ORDER BY ID ), * FROM TEST; ### FIRST_VALUE
    FIRST_VALUE(value) [{RESPECT|IGNORE} NULLS]
    OVER windowNameOrSpecification
`FIRST_VALUE ( value )` |
| `RESPECT` |
| `IGNORE` |
| `NULLS`
`OVER windowNameOrSpecification`
Returns the first value in a window. If `IGNORE NULLS` is specified null values are skipped and the function returns first non-null value, if any. Window functions in H2 may require a lot of memory for large queries. Example: SELECT FIRST_VALUE(X) OVER (ORDER BY ID), * FROM TEST; SELECT FIRST_VALUE(X) IGNORE NULLS OVER (PARTITION BY CATEGORY ORDER BY ID), * FROM TEST; ### LAST_VALUE
    LAST_VALUE(value) [{RESPECT|IGNORE} NULLS]
    OVER windowNameOrSpecification
`LAST_VALUE ( value )` |
| `RESPECT` |
| `IGNORE` |
| `NULLS`
`OVER windowNameOrSpecification`
Returns the last value in a window. If `IGNORE NULLS` is specified null values are skipped and the function returns last non-null value before them, if any; if there is no non-null value it returns `NULL`. Note that the last value is actually a value in the current group of rows if window order clause is specified and window frame clause is not specified. Window functions in H2 may require a lot of memory for large queries. Example: SELECT LAST_VALUE(X) OVER (ORDER BY ID), * FROM TEST; SELECT LAST_VALUE(X) IGNORE NULLS OVER ( PARTITION BY CATEGORY ORDER BY ID RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ), * FROM TEST; ### NTH_VALUE
    NTH_VALUE(value, nInt) [FROM {FIRST|LAST}] [{RESPECT|IGNORE} NULLS]
    OVER windowNameOrSpecification
`NTH_VALUE ( value , nInt )` |
`FROM` |
| `FIRST` |
| `LAST` |
| `RESPECT` |
| `IGNORE` |
| `NULLS`
`OVER windowNameOrSpecification`
Returns the value in a row with a specified relative number in a window. Relative row number must be positive. If `FROM LAST` is specified rows a counted backwards from the last row. If `IGNORE NULLS` is specified rows with null values in selected expression are skipped. If number of considered rows is less than specified relative number this function returns `NULL`. Note that the last row is actually a last row in the current group of rows if window order clause is specified and window frame clause is not specified. Window functions in H2 may require a lot of memory for large queries. Example: SELECT NTH_VALUE(X) OVER (ORDER BY ID), * FROM TEST; SELECT NTH_VALUE(X) IGNORE NULLS OVER ( PARTITION BY CATEGORY ORDER BY ID RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ), * FROM TEST; ### RATIO_TO_REPORT
    RATIO_TO_REPORT(value)
    OVER windowNameOrSpecification
`RATIO_TO_REPORT ( value )`
`OVER windowNameOrSpecification`
Returns the ratio of a value to the sum of all values. If argument is `NULL` or sum of all values is 0, then the value of function is `NULL`. Window ordering and window frame clauses are not allowed for this function. Window functions in H2 may require a lot of memory for large queries. Example: SELECT X, RATIO_TO_REPORT(X) OVER (PARTITION BY CATEGORY), CATEGORY FROM TEST;