Functions

Index

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 COSH COT SIN SINH TAN TANH 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 CONCAT_WS 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 TO_CHAR TRANSLATE

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 ARRAY_CONTAINS AUTOCOMMIT CANCEL_SESSION CASEWHEN Function CAST COALESCE CONVERT CURRVAL CSVREAD CSVWRITE DATABASE

DATABASE_PATH DECODE DISK_SPACE_USED FILE_READ GREATEST IDENTITY IFNULL LEAST LOCK_MODE LOCK_TIMEOUT MEMORY_FREE MEMORY_USED NEXTVAL

NULLIF NVL2 READONLY ROWNUM SCHEMA SCOPE_IDENTITY SESSION_ID SET TABLE TRANSACTION_ID TRUNCATE_VALUE USER LEALONE_VERSION

Details

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

AVG

AVG ( [ DISTINCT ] { numeric } )

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)

BOOL_AND

BOOL_AND(boolean)

Returns true if all expressions are true. If no rows are selected, the result is NULL. Aggregates are only allowed in select statements.

Example:

BOOL_AND(ID>10)

BOOL_OR

BOOL_OR(boolean)

Returns true if any expression is true. If no rows are selected, the result is NULL. Aggregates are only allowed in select statements.

Example:

BOOL_OR(NAME LIKE 'W%')

COUNT

COUNT( { * | { [ DISTINCT ] expression } } )

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

GROUP_CONCAT

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

Concatenates strings with a separator. 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:

GROUP_CONCAT(NAME ORDER BY ID SEPARATOR ', ')

MAX

MAX(value)

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)

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 ] { numeric } )

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)

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

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

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

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

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)

ABS

ABS ( { numeric } )

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. The returned value is of the same data type as the parameter.

Example:

ABS(ID)

ACOS

ACOS(numeric)

Calculate the arc cosine. See also Java Math.acos. This method returns a double.

Example:

ACOS(D)

ASIN

ASIN(numeric)

Calculate the arc sine. See also Java Math.asin. This method returns a double.

Example:

ASIN(D)

ATAN

ATAN(numeric)

Calculate the arc tangent. See also Java Math.atan. This method returns a double.

Example:

ATAN(D)

COS

COS(numeric)

Calculate the trigonometric cosine. See also Java Math.cos. This method returns a double.

Example:

COS(ANGLE)

COSH

COSH(numeric)

Calculate the hyperbolic cosine. See also Java Math.cosh. This method returns a double.

Example:

COSH(X)

COT

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)

Calculate the trigonometric sine. See also Java Math.sin. This method returns a double.

Example:

SIN(ANGLE)

SINH

SINH(numeric)

Calculate the hyperbolic sine. See also Java Math.sinh. This method returns a double.

Example:

SINH(ANGLE)

TAN

TAN(numeric)

Calculate the trigonometric tangent. See also Java Math.tan. This method returns a double.

Example:

TAN(ANGLE)

TANH

TANH(numeric)

Calculate the hyperbolic tangent. See also Java Math.tanh. This method returns a double.

Example:

TANH(X)

ATAN2

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)

The bitwise AND operation. This method returns a long. See also Java operator &.

Example:

BITAND(A, B)

BITOR

BITOR(long, long)

The bitwise OR operation. This method returns a long. See also Java operator |.

Example:

BITOR(A, B)

BITXOR

BITXOR(long, long)

The bitwise XOR operation. This method returns a long. See also Java operator ^.

Example:

BITXOR(A, B)

MOD

MOD(long, long)

The modulo operation. This method returns a long. See also Java operator %.

Example:

MOD(A, B)

CEILING

{ CEILING | CEIL } (numeric)

See also Java Math.ceil. This method returns a double.

Example:

CEIL(A)

DEGREES

DEGREES(numeric)

See also Java Math.toDegrees. This method returns a double.

Example:

DEGREES(A)

EXP

EXP(numeric)

See also Java Math.exp. This method returns a double.

Example:

EXP(A)

FLOOR

FLOOR(numeric)

See also Java Math.floor. This method returns a double.

Example:

FLOOR(A)

LOG

{ LOG | LN } (numeric)

See also Java Math.log. In the PostgreSQL mode, LOG(x) is base 10. This method returns a double.

Example:

LOG(A)

LOG10

LOG10(numeric)

See also Java Math.log10 (in Java 5). This method returns a double.

Example:

LOG10(A)

RADIANS

RADIANS(numeric)

See also Java Math.toRadians. This method returns a double.

Example:

RADIANS(A)

SQRT

SQRT(numeric)

See also Java Math.sqrt. This method returns a double.

Example:

SQRT(A)

PI

PI()

See also Java Math.PI. This method returns a double.

Example:

PI()

POWER

POWER(numeric, numeric)

See also Java Math.pow. This method returns a double.

Example:

POWER(A, B)

RAND

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

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

Rounds to a number of digits, or to the nearest long if the number of digits if not set. This method returns a double.

Example:

ROUND(VALUE, 2)

ROUNDMAGIC

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

Generates a number of cryptographically secure random numbers. This method returns bytes.

Example:

CALL SECURE_RAND(16)

SIGN

SIGN ( { numeric } )

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

Example:

SIGN(VALUE)

ENCRYPT

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)

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

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.

Example:

TRUNCATE(VALUE, 2)

COMPRESS

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)

Expands data that was compressed using the COMPRESS function. This method returns bytes.

Example:

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

ZERO

ZERO()

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

Example:

ZERO()

ASCII

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)

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 )

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)

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 )

Returns the character that represents the ASCII value. This method returns a string.

Example:

CHAR(65)

CONCAT

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 [,...])

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)

Returns the difference between the sounds of two strings. This method returns an int.

Example:

DIFFERENCE(T1.NAME, T2.NAME)

HEXTORAW

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)

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

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:

INSTR(EMAIL,'@')

INSERT Function

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 )

Converts a string to lowercase.

Example:

LOWER(NAME)

UPPER

{ UPPER | UCASE } ( string )

Converts a string to uppercase.

Example:

UPPER(NAME)

LEFT

LEFT(string, int)

Returns the leftmost number of characters.

Example:

LEFT(NAME, 3)

RIGHT

RIGHT(string, int)

Returns the rightmost number of characters.

Example:

RIGHT(NAME, 3)

LOCATE

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)

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

Example:

POSITION('.', NAME)

LPAD

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

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)

Removes all leading spaces from a string.

Example:

LTRIM(NAME)

RTRIM

RTRIM(string)

Removes all trailing spaces from a string.

Example:

RTRIM(NAME)

TRIM

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)

Replaces each substring that matches a regular expression. For details, see the Java String.replaceAll() method. If any parameter is null, the result is null.

Example:

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

REPEAT

REPEAT(string, int)

Returns a string repeated some number of times.

Example:

REPEAT(NAME || ' ', 10)

REPLACE

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)

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

Example:

SOUNDEX(NAME)

SPACE

SPACE(int)

Returns a string consisting of a number of spaces.

Example:

SPACE(80)

STRINGDECODE

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)

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)

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

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)

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

Example:

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

XMLATTR

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://lealone.com'))

XMLNODE

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://lealone.com'), 'lealone')

XMLCOMMENT

XMLCOMMENT(commentString)

Creates an XML comment. Two dashes (--) are converted to - -. This method returns a string.

Example:

CALL XMLCOMMENT('Test')

XMLCDATA

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

Returns the XML declaration. The result is always <?xml version=1.0?>.

Example:

CALL XMLSTARTDOC()

XMLTEXT

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

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

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

ARRAY_GET

ARRAY_GET(arrayExpression, indexExpression)

Returns one element of an array. This method returns a string.

Example:

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

ARRAY_LENGTH

ARRAY_LENGTH(arrayExpression)

Returns the length of an array.

Example:

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

ARRAY_CONTAINS

ARRAY_CONTAINS(arrayExpression, value)

Returns a boolean true if the array contains the value.

Example:

CALL ARRAY_CONTAINS(('Hello', 'World'), 'Hello')

AUTOCOMMIT

AUTOCOMMIT()

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

Example:

AUTOCOMMIT()

CANCEL_SESSION

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)

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)

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.

Example:

CAST(NAME AS INT);
CAST(65535 AS BINARY);
CAST(CAST('FFFF' AS BINARY) AS INT);

COALESCE

{ COALESCE | NVL } (aValue, bValue [,...])

Returns the first value that is not null.

Example:

COALESCE(A, B, C)

CONVERT

CONVERT(value, dataType)

Converts a value to another data type.

Example:

CONVERT(NAME, INT)

CURRVAL

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

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

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

Returns the name of the database.

Example:

CALL DATABASE();

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 [,...])

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)

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

FILE_READ

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('~/.lealone.server.properties')) LEN;
SELECT FILE_READ('http://localhost:8182/stylesheet.css', NULL) CSS;

GREATEST

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

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)

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

Example:

CALL IFNULL(NULL, '');

LEAST

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

Returns the current lock mode. See SET LOCK_MODE. This method returns an int.

Example:

CALL LOCK_MODE();

LOCK_TIMEOUT

LOCK_TIMEOUT()

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

Example:

LOCK_TIMEOUT()

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

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 )

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)

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

Example:

NULLIF(A, B)

NVL2

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

Returns true if the database is read-only.

Example:

READONLY()

ROWNUM

{ ROWNUM() } | { ROW_NUMBER() OVER() }

Returns the number of the current row. This method returns a long. 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). To get the row number after ordering and grouping, use a subquery.

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

Returns the name of the default  schema for this session.

Example:

CALL SCHEMA()

SCOPE_IDENTITY

SCOPE_IDENTITY()

Returns the last inserted identity value for this session for the current scope. Changes within triggers and Java functions are ignored. See also IDENTITY(). This method returns a long.

Example:

CALL SCOPE_IDENTITY();

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)

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 = expression } [,...] )

Returns the result set. TABLE_DISTINCT removes duplicate rows.

Example:

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

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

USER

{ USER | CURRENT_USER } ()

Returns the name of the current user of this session.

Example:

CURRENT_USER()

LEALONE_VERSION

LEALONE_VERSION()

Returns the lealone version as a String.

Example:

LEALONE_VERSION()

CURRENT_DATE

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

Returns the current date. This method always returns the same value within a transaction.

Example:

CURRENT_DATE()

CURRENT_TIME

{ CURRENT_TIME [ () ] | CURTIME() }

Returns the current time. This method always returns the same value within a transaction.

Example:

CURRENT_TIME()

CURRENT_TIMESTAMP

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

Returns the current timestamp. The precision parameter for nanoseconds precision is optional. This method always returns the same value within a transaction.

Example:

CURRENT_TIMESTAMP()

DATEADD

{ DATEADD| TIMESTAMPADD } (unitString, addInt, 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. This method returns a timestamp.

Example:

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

DATEDIFF

{ DATEDIFF | TIMESTAMPDIFF } (unitString, aTimestamp, bTimestamp)

Returns the the number of crossed unit boundaries between two timestamps. This method returns a long. 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)

Returns the name of the day (in English).

Example:

DAYNAME(CREATED)

DAY_OF_MONTH

DAY_OF_MONTH(date)

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

Example:

DAY_OF_MONTH(CREATED)

DAY_OF_WEEK

DAY_OF_WEEK(date)

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

Example:

DAY_OF_WEEK(CREATED)

DAY_OF_YEAR

DAY_OF_YEAR(date)

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

Example:

DAY_OF_YEAR(CREATED)

EXTRACT

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

Returns a specific value from a timestamps. This method returns an int.

Example:

EXTRACT(SECOND FROM CURRENT_TIMESTAMP)

FORMATDATETIME

FORMATDATETIME ( timestamp, 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. 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(timestamp)

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

Example:

HOUR(CREATED)

MINUTE

MINUTE(timestamp)

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

Example:

MINUTE(CREATED)

MONTH

MONTH(timestamp)

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

Example:

MONTH(CREATED)

MONTHNAME

MONTHNAME(date)

Returns the name of the month (in English).

Example:

MONTHNAME(CREATED)

PARSEDATETIME

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

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

Example:

QUARTER(CREATED)

SECOND

SECOND(timestamp)

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

Example:

SECOND(CREATED)

WEEK

WEEK(timestamp)

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

Example:

WEEK(CREATED)

YEAR

YEAR(timestamp)

Returns the year from a timestamp.

Example:

YEAR(CREATED)