Exasol 6.2 is no longer supported, and this documentation is no longer maintained or updated.

Scalar Functions

Scalar functions receive an input value, and based on this input value the result is delivered. This function can be used with constant values, the column elements of a table (view) as well as with compound value expressions.

Example

SELECT SIN(1);
SELECT LENGTH(s) FROM t;
SELECT EXP(1+ABS(n)) FROM t;

Scalar functions usually expect a special data type for their arguments. If it is not specified, then an implicit conversion of the data type is attempted, or an error message is displayed.

Numeric Function

Numeric functions are given a numeric value as input and typically deliver a numeric value as output.

The following are the numeric functions supported:

ACOS

ATAN

CEIL[ING]

COSH

DEGREES

EXP

LN

LOG10

MOD

POWER

RAND[OM]

SIGN

SINH

SQRT

TAN

TANH

TO_CHAR (number)

TO_NUMBER

TRUNC[ATE] (number)

String Functions

String functions can either return a string (for example, LPAD) or a numeric value (for example, LENGTH).

ASCII

BIT_LENGTH

CHARACTER_LENGTH

CH[A]R

COLOGNE_PHONETIC

CONCAT

DUMP

EDIT_DISTANCE

INITCAP

INSERT

INSTR

LCASE

LENGTH

LOWER

LPAD

MID

POSITION

REGEXP_REPLACE

REPEAT

REVERSE

RPAD

SOUNDEX

SUBSTR[ING]

TO_CHAR (datetime)

TO_CHAR (number)

TRIM

UNICODE

UPPER

Date/Time Functions

Date/Time functions manipulate the DATE, TIMESTAMP, TIMESTAMP WITH LOCAL TIME ZONE, and INTERVAL data types.

ADD_DAYS
ADD_HOURS
ADD_MINUTES
ADD_MONTHS
ADD_SECONDS
ADD_WEEKS
ADD_YEARS
CONVERT_TZ
CURDATE
CURRENT_DATE
CURRENT_TIMESTAMP
DATE_TRUNC
DAY
DAYS_BETWEEN
DBTIMEZONE
EXTRACT
FROM_POSIX_TIME
HOUR
HOURS_BETWEEN
LOCALTIMESTAMP
MINUTE
MINUTES_BETWEEN
MONTH
MONTHS_BETWEEN
NOW
NUMTODSINTERVAL
NUMTOYMINTERVAL
POSIX_TIME
ROUND (datetime)
SECOND
SECONDS_BETWEEN
SESSIONTIMEZONE
SYSDATE
SYSTIMESTAMP
TO_CHAR (datetime)
TO_DATE
TO_DSINTERVAL
TO_TIMESTAMP
TO_YMINTERVAL
TRUNC[ATE] (datetime)
WEEK
YEAR
YEARS_BETWEEN

Geospatial Functions

To analyze geospatial data, there are many functions available. For more information on geospatial data, refer to the Geospatial Data section.

ST_*

Bitwise Function

Bitwise functions can compute bit operations on numerical values.

BIT_AND
BIT_CHECK
BIT_LROTATE
BIT_LSHIFT
BIT_NOT
BIT_OR
BIT_RROTATE
BIT_RSHIFT
BIT_SET
BIT_TO_NUM
BIT_XOR

Conversion Functions

Conversion functions can be used to convert values to other data types.

CAST
CONVERT
IS_*
NUMTODSINTERVAL
NUMTOYMINTERVAL
TO_CHAR (datetime)
TO_CHAR (number)
TO_DATE
TO_DSINTERVAL
TO_NUMBER
TO_TIMESTAMP
TO_YMINTERVAL

Functions for Hierarchical Queries

The following functions can be used in combination with CONNECT BY queries.

CONNECT_BY_ISCYCLE
CONNECT_BY_ISLEAF
LEVEL
SYS_CONNECT_BY_PATH

JSON Functions

JSON functions extract values from JSON objects stored as strings.

JSON_VALUE
JSON_EXTRACT

Other Scalar Functions

Those functions that cannot be allocated to one of the above categories are listed here.