H2 Logo
English Japanese
Search:

Highlight keyword(s)
Home
Quickstart
Installation
Tutorial
Features
Performance
Advanced Topics
JaQu
Download

Reference
SQL Grammar
Functions
Data Types
Javadoc
Docs as PDF
Error Analyzer

Appendix
Build
History & Roadmap
Links
FAQ
License

 

Functions

Aggregate Functions

AVG
BOOL_AND
BOOL_OR
COUNT
GROUP_CONCAT
MAX
MIN
SUM
SELECTIVITY
STDDEV_POP
STDDEV_SAMP
VAR_POP
VAR_SAMP

Numeric Functions

ABS
ACOS
ASIN
ATAN
COS
COT
SIN
TAN
ATAN2
BITAND
BITOR
BITXOR
MOD
CEILING
DEGREES
EXP
FLOOR
LOG
LOG10
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
DIFFERENCE
HEXTORAW
RAWTOHEX
INSTR
INSERT Function
LOWER
UPPER
LEFT
RIGHT
LOCATE
POSITION
LPAD
RPAD
LTRIM
RTRIM
TRIM
REGEXP_REPLACE
REPEAT
REPLACE
SOUNDEX
SPACE
STRINGDECODE
STRINGENCODE
STRINGTOUTF8
SUBSTRING
UTF8TOSTRING
XMLATTR
XMLNODE
XMLCOMMENT
XMLCDATA
XMLSTARTDOC
XMLTEXT

Time and Date Functions

CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
DATEADD
DATEDIFF
DAYNAME
DAY_OF_MONTH
DAY_OF_WEEK
DAY_OF_YEAR
EXTRACT
FORMATDATETIME
HOUR
MINUTE
MONTH
MONTHNAME
PARSEDATETIME
QUARTER
SECOND
WEEK
YEAR

System Functions

ARRAY_GET
ARRAY_LENGTH
AUTOCOMMIT
CANCEL_SESSION
CASEWHEN Function
CAST
COALESCE
CONVERT
CURRVAL
CSVREAD
CSVWRITE
DATABASE
DATABASE_PATH
FILE_READ
GREATEST
IDENTITY
IFNULL
LEAST
LOCK_MODE
LOCK_TIMEOUT
LINK_SCHEMA
MEMORY_FREE
MEMORY_USED
NEXTVAL
NULLIF
READONLY
ROWNUM
SCHEMA
SESSION_ID
SET
TABLE
USER

AVG

AVG([DISTINCT] {int | long | decimal | double}): value

The average (mean) value.
Aggregates are only allowed in select statements.

Example:
AVG(X)

BOOL_AND

BOOL_AND(boolean): boolean

Returns true if all expressions are true.
Aggregates are only allowed in select statements.

Example:
BOOL_AND(ID>10)

BOOL_OR

BOOL_OR(boolean): boolean

Returns true if any expression is true.
Aggregates are only allowed in select statements.

Example:
BOOL_OR(NAME LIKE 'W%')

COUNT

COUNT(*) | COUNT([DISTINCT] expression): int

The count of all row, or of the non-null values.
Aggregates are only allowed in select statements.

Example:
COUNT(*)

GROUP_CONCAT

GROUP_CONCAT([DISTINCT] string [ORDER BY {expression [ASC|DESC]}[,...]] [SEPARATOR expression]): string

Concatenates strings with a separator. The default separator is a ',' (without space).
Aggregates are only allowed in select statements.

Example:
GROUP_CONCAT(NAME ORDER BY ID SEPARATOR ', ')

MAX

MAX(value): value

The highest value.
Aggregates are only allowed in select statements.

Example:
MAX(NAME)

MIN

MIN(value): value

The lowest value.
Aggregates are only allowed in select statements.

Example:
MIN(NAME)

SUM

SUM([DISTINCT] {int | long | decimal | double}): value

The sum of all values.
Aggregates are only allowed in select statements.

Example:
SUM(X)

SELECTIVITY

SELECTIVITY(value): int

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

STDDEV_POP

STDDEV_POP([DISTINCT] double): double

The population standard deviation.
Aggregates are only allowed in select statements.

Example:
STDDEV_POP(X)

STDDEV_SAMP

STDDEV_SAMP([DISTINCT] double): double

The sample standard deviation.
Aggregates are only allowed in select statements.

Example:
STDDEV(X)

VAR_POP

VAR_POP([DISTINCT] double): double

The population variance (square of the population standard deviation).
Aggregates are only allowed in select statements.

Example:
VAR_POP(X)

VAR_SAMP

VAR_SAMP([DISTINCT] double): double

The sample variance (square of the sample standard deviation).
Aggregates are only allowed in select statements.

Example:
VAR_SAMP(X)

ABS

ABS({int | long | decimal | double}): value

See also Java Math.abs.
Please note that Math.abs(Integer.MIN_VALUE) == Integer.MIN_VALUE and
Math.abs(Long.MIN_VALUE) == Long.MIN_VALUE.

Example:
ABS(ID)

ACOS

ACOS(double): double

See also Java Math.* functions.

Example:
ACOS(D)

ASIN

ASIN(double): double

See also Java Math.* functions.

Example:
ASIN(D)

ATAN

ATAN(double): double

See also Java Math.* functions.

Example:
ATAN(D)

COS

COS(double): double

See also Java Math.* functions.

Example:
COS(ANGLE)

COT

COT(double): double

See also Java Math.* functions.

Example:
COT(ANGLE)

SIN

SIN(double): double

See also Java Math.* functions.

Example:
SIN(ANGLE)

TAN

TAN(double): double

See also Java Math.* functions.

Example:
TAN(ANGLE)

ATAN2

ATAN2(double, double): double

See also Java Math.atan2.

Example:
ATAN2(X, Y)

BITAND

BITAND(int, int): int

See also Java operator &.

Example:
BITAND(A, B)

BITOR

BITOR(int, int): int

See also Java operator |.

Example:
BITOR(A, B)

BITXOR

BITXOR(int, int): int

See also Java operator ^.

Example:
BITXOR(A, B)

MOD

MOD(int, int): int

See also Java operator %.

Example:
MOD(A, B)

CEILING

CEILING(double): double

See also Java Math.ceil.

Example:
LOG(A)

DEGREES

DEGREES(double): double

See also Java Math.toDegrees.

Example:
DEGREES(A)

EXP

EXP(double): double

See also Java Math.exp.

Example:
EXP(A)

FLOOR

FLOOR(double): double

See also Java Math.floor.

Example:
FLOOR(A)

LOG

LOG(double): double

See also Java Math.log.

Example:
LOG(A)

LOG10

LOG10(double): double

See also Java Math.log10 (in Java 5).

Example:
LOG10(A)

RADIANS

RADIANS(double): double

See also Java Math.toRadians.

Example:
RADIANS(A)

SQRT

SQRT(double): double

See also Java Math.sqrt.

Example:
SQRT(A)

PI

PI(): double

See also Java Math.PI.

Example:
PI()

POWER

POWER(double, double): double

See also Java Math.pow.

Example:
POWER(A, B)

RAND

RAND([int]): double

Calling the function without parameter returns the next a pseudo random number.
Calling it with an parameter seeds the session's random number generator.

Example:
RAND()

RANDOM_UUID

RANDOM_UUID(): UUID

Returns a new UUID with 122 pseudo random bits.

Example:
RANDOM_UUID()

ROUND

ROUND(double, digitsInt): double

Rounds to a number of digits.

Example:
ROUND(VALUE, 2)

ROUNDMAGIC

ROUNDMAGIC(double): double

This function rounds numbers in a good way but slow:
- special handling for numbers around 0
- only numbers <= +/-1000000000000
- convert to a string
- check the last 4 characters:
'000x' becomes '0000'
'999x' becomes '999999' (this is rounded automatically).

Example:
ROUNDMAGIC(VALUE/3*3)

SECURE_RAND

SECURE_RAND(int): bytes

Generates a number of cryptographically secure random numbers.

Example:
CALL SECURE_RAND(16)

SIGN

SIGN({int | long | decimal | double}): int

Returns -1 if the value is smaller 0, 0 if zero, and otherwise 1.

Example:
SIGN(VALUE)

ENCRYPT

ENCRYPT(algorithmString, keyBytes, dataBytes): bytes

Encrypts data using a key. Supported algorithms are XTEA and AES.
The block size is 16 bytes.

Example:
CALL ENCRYPT('AES', '00', STRINGTOUTF8('Test'))

DECRYPT

DECRYPT(algorithmString, keyBytes, dataBytes): bytes

Decrypts data using a key. Supported algorithms are XTEA and AES.
The block size is 16 bytes.

Example:
CALL TRIM(CHAR(0) FROM UTF8TOSTRING(DECRYPT('AES', '00', '3fabb4de8f1ee2e97d7793bab2db1116')))

HASH

HASH(algorithmString, dataBytes, iterationInt): bytes

Calculate the hash value using an algorithm, and repeat this process for a number of iterations.
Currently, the only algorithm supported is SHA256.

Example:
CALL HASH('SHA256', STRINGTOUTF8('Password'), 1000)

TRUNCATE

TRUNCATE(double, digitsInt): double

Truncates to a number of digits (to the next value closer to 0).

Example:
TRUNCATE(VALUE, 2)

COMPRESS

COMPRESS(dataBytes [, algorithmString]): bytes

Compresses the data using the specified compression algorithm.
Supported algorithms are:
LZF (fast but lower compression; default),
DEFLATE (higher compression).
Compression does not always reduce size.
Very small objects and objects with little redundancy may get larger.

Example:
COMPRESS(STRINGTOUTF8('Test'))

EXPAND

EXPAND(bytes): bytes

Expands data that was compressed using the COMPRESS function.

Example:
UTF8TOSTRING(EXPAND(COMPRESS(STRINGTOUTF8('Test'))))

ZERO

ZERO(): int

Returns the value 0. This function can be used even if numeric literals are disabled.

Example:
ZERO()

ASCII

ASCII(string): int

Returns the ASCII value of the first character in the string.

Example:
ASCII('Hi')

BIT_LENGTH

BIT_LENGTH(string): int

Returns the number of bits in a string.
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): int

Returns the number of characters in a string.
For BLOB, CLOB, BYTES and JAVA_OBJECT, the precision is used.

Example:
LENGTH(NAME)

OCTET_LENGTH

OCTET_LENGTH(string): int

Returns the number of bytes in a string.
For BLOB, CLOB, BYTES and JAVA_OBJECT, the precision is used.
Each character needs 2 bytes.

Example:
OCTET_LENGTH(NAME)

CHAR

{CHAR | CHR}(int): string

Returns the character that represents the ASCII value.

Example:
CHAR(65)

CONCAT

CONCAT(string, string [,...]): string

Combines strings.

Example:
CONCAT(NAME, '!')

DIFFERENCE

DIFFERENCE(string, string): int

Returns the difference between the sounds of two strings.

Example:
DIFFERENCE(T1.NAME, T2.NAME)

HEXTORAW

HEXTORAW(string): 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): string

Converts a string to the hex representation.
4 hex characters per string character are used.

Example:
RAWTOHEX(DATA)

INSTR

INSTR(string, searchString, [, startInt]): int

Returns the location of a search string in a string (s).
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:
INSTR(EMAIL,'@')

INSERT Function

INSERT(originalString, startInt, lengthInt, addString): string

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.

Example:
INSERT(NAME, 1, 1, ' ')

LOWER

{LOWER | LCASE}(string): string

Converts a string to lowercase.

Example:
LOWER(NAME)

UPPER

{UPPER | UCASE}(string): string

Converts a string to uppercase.

Example:
UPPER(NAME)

LEFT

LEFT(string, int): string

Returns the leftmost number of characters.

Example:
LEFT(NAME, 3)

RIGHT

RIGHT(string, int): string

Returns the rightmost number of characters.

Example:
RIGHT(NAME, 3)

LOCATE

LOCATE(searchString, string [, startInt]): int

Returns the location of a search string in a string (s).
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): int

Returns the location of a search string in a string (s).
See also LOCATE.

Example:
POSITION('.', NAME)

LPAD

LPAD(string, int[, paddingString]): string

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]): string

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

Removes all leading spaces from a string.

Example:
LTRIM(NAME)

RTRIM

RTRIM(string): string

Removes all trailing spaces from a string.

Example:
RTRIM(NAME)

TRIM

TRIM([{LEADING | TRAILING | BOTH} [string] FROM]
string): string

Removes all leading spaces, trailing spaces, or spaces at both ends, from a string.
It is possible to remove other characters as well.

Example:
TRIM(BOTH '_' FROM NAME)

REGEXP_REPLACE

REGEXP_REPLACE(inputString, regexString, replacementString): string

Replaces each substring that matches a regular expression.
For details, see the Java String.replaceAll() method.

Example:
REGEXP_REPLACE('Hello    World', ' +', ' ')

REPEAT

REPEAT(string, int): string

Returns a string repeated some number of times.

Example:
REPEAT(NAME || ' ', 10)

REPLACE

REPLACE(string, searchString [, replacementString]): string

Replaces all occurrences of a search string in a text with another string.
If no replacement is specified, the search string is just removed from the original string.

Example:
REPLACE(NAME, ' ')

SOUNDEX

SOUNDEX(string): string

Returns a four character code representing the sound of a string.
See also http://www.archives.gov/genealogy/soundex.html .

Example:
SOUNDEX(NAME)

SPACE

SPACE(int): string

Returns a string consisting of a number of spaces.

Example:
SPACE(80)

STRINGDECODE

STRINGDECODE(string): string

Converts a encoded string using the Java string literal encoding format.
Special characters are \b, \t, \n, \f, \r, \", \\, \<octal>, \u<unicode>.

Example:
CALL STRINGENCODE(STRINGDECODE('Lines 1\nLine 2'))

STRINGENCODE

STRINGENCODE(string): 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>.

Example:
CALL STRINGENCODE(STRINGDECODE('Lines 1\nLine 2'))

STRINGTOUTF8

STRINGTOUTF8(string): bytes

Encodes a string to a byte array using the UTF8 encoding format.

Example:
CALL UTF8TOSTRING(STRINGTOUTF8('This is a test'))

SUBSTRING

{SUBSTRING | SUBSTR}(string, startInt [, lengthInt]): string

Returns a substring of a string starting at a position.
The length is optional.
Also supported is: SUBSTRING(string FROM start [FOR length]).

Example:
SUBSTR(NAME, 1)

UTF8TOSTRING

UTF8TOSTRING(bytes): string

Decodes a byte array in the UTF8 format to a string.

Example:
CALL UTF8TOSTRING(STRINGTOUTF8('This is a test'))

XMLATTR

XMLATTR(nameString, valueString): string

Creates an XML attribute element of the form name="value".
The value is encoded as XML text.

Example:
CALL XMLNODE('a', XMLATTR('href', 'http://h2database.com'))

XMLNODE

XMLNODE(elementString [, attributesString [, contentString]]): string

Create an XML node element.

Example:
CALL XMLNODE('a', XMLATTR('href', 'http://h2database.com'), 'H2')

XMLCOMMENT

XMLCOMMENT(commentString): string

Creates an XML comment. Two dashes (--) are converted to - -.

Example:
CALL XMLCOMMENT('Test')

XMLCDATA

XMLCDATA(valueString): string

Creates an XML CDATA element. If the value contains ']]>', an XML text element is created instead.

Example:
CALL XMLCDATA('data')

XMLSTARTDOC

XMLSTARTDOC(): string

The string '<?xml version="1.0"?>' is returned.

Example:
CALL XMLSTARTDOC()

XMLTEXT

XMLTEXT(valueString): string

Creates an XML text element.

Example:
CALL XMLTEXT('test')

ARRAY_GET

ARRAY_GET(arrayExpression, indexExpression): value

Returns one element of an array.

Example:
CALL ARRAY_GET(('Hello', 'World'), 2)

ARRAY_LENGTH

ARRAY_GET(arrayExpression): int

Returns the length of an array.

Example:
CALL ARRAY_LENGTH(('Hello', 'World'))

AUTOCOMMIT

AUTOCOMMIT(): boolean

Returns true if auto commit is switched on for this session.

Example:
AUTOCOMMIT()

CANCEL_SESSION

CANCEL_SESSION(sessionInt): boolean

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

Returns 'a' if the boolean expression is true, otherwise 'b'.

Example:
CASEWHEN(ID=1, 'A', 'B')

CAST

CAST(value AS dataType): value

Converts a value to another data type.

Example:
CAST(NAME AS INT)

COALESCE

COALESCE(aValue, bValue [,...]): value

Returns the first value that is not null.

Example:
COALESCE(A, B, C)

CONVERT

CONVERT(value, dataType): value

Converts a value to another data type.

Example:
CONVERT(NAME, INT)

CURRVAL

CURRVAL([schemaName, ] sequenceString): long

Returns the current (last) value of the sequence.
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).

Example:
CURRVAL('TEST_SEQ')

CSVREAD

CSVREAD(fileNameString [, columnNamesString [, charsetString [, fieldSeparatorString [, fieldDelimiterString
[, escapeCharacterString [, nullString]]]]]]): resultSet

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 they are read from the file, otherwise (or if they are set to NULL) the first line
of the file is interpreted as the column names.
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 the null string is parsed as NULL.
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.zip.
Admin rights are required to execute this command.

Example:
CALL CSVREAD('test.csv');
-- Read a file containing the columns ID, NAME with UTF-8 encoding and the pipe (|) as field separator
CALL CSVREAD('test2.csv', 'ID|NAME', 'UTF-8', '|');
-- Read a semicolon-separated file
SELECT * FROM CSVREAD('data/test.csv', NULL, NULL, ';');

CSVWRITE

CSVWRITE(fileNameString, queryString [, charsetString [, fieldSeparatorString [, fieldDelimiterString
[, escapeCharacterString [, nullString [, lineSeparatorString]]]]]]): int

Writes a CSV (comma separated values).
The file is overwritten if it exists.
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 null string is used when writing NULL (by default nothing is written when NULL appears).
The default line separator is the default value for this system ('line.separator' system property).
The returned value is the number or rows written.
Admin rights are required to execute this command.

Example:
CALL CSVWRITE('test.csv', 'SELECT * FROM TEST');
-- Write a file with UTF-8 encoding and the pipe (|) as field separator
CALL CSVWRITE('test2.csv', 'SELECT * FROM TEST', 'UTF-8', '|');

DATABASE

DATABASE(): string

Returns the name of the database.

Example:
CALL DATABASE();

DATABASE_PATH

DATABASE_PATH(): string

Returns the directory of the database files and the database name, if it is file based.
Returns NULL otherwise.

Example:
CALL DATABASE_PATH();

FILE_READ

FILE_READ(fileNameString [,encodingString]): value

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

GREATEST

GREATEST(aValue, bValue [,...]): value

Returns the largest value that is not NULL, or NULL if all values are NULL.

Example:
CALL GREATEST(1, 2, 3);

IDENTITY

IDENTITY(): long

Returns the last inserted identity value for this session.

Example:
CALL IDENTITY();

IFNULL

IFNULL(aValue, bValue): value

Returns the value of 'a' if it is not null, otherwise 'b'.

Example:
CALL IFNULL(NULL, '');

LEAST

LEAST(aValue, bValue [,...]): value

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

Returns the current lock mode. See SET LOCK_MODE.

Example:
CALL LOCK_MODE();

LOCK_TIMEOUT

LOCK_TIMEOUT(): int

Returns the lock timeout of the current session (in milliseconds).

Example:
LOCK_TIMEOUT()

LINK_SCHEMA

LINK_SCHEMA(targetSchemaString, driverString, urlString,
userString, passwordString, sourceSchemaString): resultSet

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.
Admin rights are required to execute this command.

Example:
CALL LINK_SCHEMA('TEST2', '', 'jdbc:h2:test2', 'sa', 'sa', 'PUBLIC');

MEMORY_FREE

MEMORY_FREE(): int

Returns the free memory in KB (where 1024 bytes is a KB).
The garbage is run before returning the value.
Admin rights are required to execute this command.

Example:
MEMORY_FREE()

MEMORY_USED

MEMORY_USED(): int

Returns the used memory in KB (where 1024 bytes is a KB).
The garbage is run before returning the value.
Admin rights are required to execute this command.

Example:
MEMORY_USED()

NEXTVAL

NEXTVAL([schemaName, ] sequenceString): long

Returns the next value of the sequence.
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).

Example:
NEXTVAL('TEST_SEQ')

NULLIF

NULLIF(aValue, bValue): value

Returns NULL if 'a' is equals to 'b', otherwise 'a'.

Example:
NULLIF(A, B)

READONLY

READONLY(): boolean

Returns true if the database is read-only.

Example:
READONLY()

ROWNUM

ROWNUM(): int

Returns the number of the current row. This function 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.

Example:
SELECT ROWNUM(), * FROM TEST

SCHEMA

SCHEMA(): string

Returns the name of the default  schema for this session.

Example:
CALL SCHEMA()

SESSION_ID

SESSION_ID(): int

Returns the unique session id number for the current database connection.
This id stays the same while the connection is open.
The database engine may re-use a session id after the connection is closed.

Example:
CALL SESSION_ID()

SET

SET(@variableName, value): 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.

Example:
SELECT X, SET(@I, IFNULL(@I, 0)+X) RUNNING_TOTAL FROM SYSTEM_RANGE(1, 10)

TABLE

TABLE|TABLE_DISTINCT( { name dataType = expression } [,..]): result set

Returns the result set. TABLE_DISTINCT removes duplicate rows.

Example:
SELECT * FROM TABLE(ID INT=(1, 2), NAME VARCHAR=('Hello', 'World'))

USER

{USER | CURRENT_USER}(): string

Returns the name of the current user of this session.

Example:
CURRENT_USER()

CURRENT_DATE

{CURRENT_DATE[()] | CURDATE() | SYSDATE | TODAY}: date

Returns the current date.

Example:
CURRENT_DATE()

CURRENT_TIME

{CURRENT_TIME[()] | CURTIME()}: time

Returns the current time.

Example:
CURRENT_TIME()

CURRENT_TIMESTAMP

{CURRENT_TIMESTAMP[([int])] | NOW([int])}: timestamp

Returns the current timestamp.
The precision parameter for nanoseconds precision is optional.

Example:
CURRENT_TIMESTAMP()

DATEADD

DATEADD(unitString, addInt, timestamp): timestamp

Adds units to a timestamp. The string indicates the unit. Use negative values to subtract units.
The same units as in the EXTRACT function are supported.

Example:
DATEADD('MONTH', 1, DATE '2001-01-31')

DATEDIFF

DATEDIFF(unitString, aTimestamp, bTimestamp): long

Returns the difference between two timestamps. The string indicates the unit.
The same units as in the EXTRACT function are supported.

Example:
DATEDIFF('YEAR', T1.CREATED, T2.CREATED)

DAYNAME

DAYNAME(date): string

Returns the name of the day (in English).

Example:
DAYNAME(CREATED)

DAY_OF_MONTH

DAY_OF_MONTH(date): int

Returns the day of the month (1-31).

Example:
DAY_OF_MONTH(CREATED)

DAY_OF_WEEK

DAY_OF_WEEK(date): int

Returns the day of the week (1 means Sunday).

Example:
DAY_OF_WEEK(CREATED)

DAY_OF_YEAR

DAY_OF_YEAR(date): int

Returns the day of the year (1-366).

Example:
DAY_OF_YEAR(CREATED)

EXTRACT

EXTRACT(
{YEAR | YY | MONTH | MM | DAY | DD | DAY_OF_YEAR | DOY | 
HOUR | HH | MINUTE | MI | SECOND | SS | MILLISECOND | MS}
FROM timestamp): int

Returns a specific value from a timestamps.

Example:
EXTRACT(SECOND FROM CURRENT_TIMESTAMP)

FORMATDATETIME

FORMATDATETIME(timestamp, formatString [, localeString [, timeZoneString]]): string

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.

Example:
CALL FORMATDATETIME(TIMESTAMP '2001-02-03 04:05:06', 'EEE, d MMM yyyy HH:mm:ss z', 'en', 'GMT')

HOUR

HOUR(timestamp): int

Returns the hour (0-23) from a timestamp.

Example:
HOUR(CREATED)

MINUTE

MINUTE(timestamp): int

Returns the minute (0-59) from a timestamp.

Example:
MINUTE(CREATED)

MONTH

MONTH(timestamp): int

Returns the month (1-12) from a timestamp.

Example:
MONTH(CREATED)

MONTHNAME

MONTHNAME(date): string

Returns the name of the month (in English).

Example:
MONTHNAME(CREATED)

PARSEDATETIME

PARSEDATETIME(string, formatString [, localeString [, timeZoneString]]): string

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(timestamp): int

Returns the quarter (1-4) from a timestamp.

Example:
QUARTER(CREATED)

SECOND

SECOND(timestamp): int

Returns the second (0-59) from a timestamp.

Example:
SECOND(CREATED)

WEEK

WEEK(timestamp): int

Returns the week (1-53) from a timestamp.
This method uses the current system locale.

Example:
WEEK(CREATED)

YEAR

YEAR(timestamp): int

Returns the year from a timestamp.

Example:
YEAR(CREATED)