H2 database logo   ▲

Home
Download
Cheat Sheet

Documentation
Quickstart
Installation
Tutorial
Features
Performance
Advanced

Reference
Commands
Functions
• Aggregate • Window

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

Numeric Functions

ABS
ACOS
ASIN
ATAN
COS
COSH
COT
SIN
SINH
TAN
TANH
ATAN2
BITAND
BITGET
BITNOT
BITOR
BITXOR
LSHIFT
RSHIFT
MOD
CEILING
DEGREES
EXP
FLOOR
LN
LOG
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
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
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
CURRENT_SCHEMA
CURRENT_CATALOG
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
SCOPE_IDENTITY
SESSION_ID
SET
TABLE
TRANSACTION_ID
TRUNCATE_VALUE
UNNEST
USER
H2VERSION

JSON Functions

JSON_OBJECT
JSON_ARRAY

Details

Click on the header to switch between railroad diagram and BNF.

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)

BITNOT

BITNOT(long)
BITNOT ( long )

The bitwise NOT operation. This method returns a long. See also Java operator ~.

Example:

BITNOT(A)

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)

LSHIFT

LSHIFT(long, int)
LSHIFT ( long , int )

The bitwise left shift operation. Shifts the first argument by the number of bits given by the second argument. This method returns a long. See also Java operator <<.

Example:

LSHIFT(A, B)

RSHIFT

RSHIFT(long, int)
RSHIFT ( long , int )

The bitwise right shift operation. Shifts the first argument by the number of bits given by the second argument. This method returns a long. See also Java operator >>.

Example:

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

Returns the smallest integer value that is greater than or equal to the argument. This method returns a double, float, or numeric value depending on type of the argument.

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 a double, float, or numeric value depending on type of the argument.

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)
LOG (
 
baseNumeric ,
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.

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 a double, float, or numeric value depending on type of the argument.

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
)

When a numeric argument is specified, truncates it to a number of digits (to the next value closer to 0) and returns a double, float, or numeric value depending on type of the argument. 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(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|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)

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 ( {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);
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'))

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 | CURDATE() | SYSDATE | TODAY
CURRENT_DATE
CURDATE ( )
SYSDATE
TODAY

Returns the current date.

These functions 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 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 always returns 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 function 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 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 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.

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

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 with default MVStore engine. 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( [ 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')

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:

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

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

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:h2:./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 ( [ 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 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;

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

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

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