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 Functions

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

Exasol supports the following numeric functions:

ABS

ACOS

ASIN

ATAN

ATAN2

CEIL[ING]

COS

COSH

COT

DEGREES

DIV

EXP

FLOOR

LN

LOG

LOG10

LOG2

MOD

PI

POWER

RADIANS

RAND[OM]

ROUND (number)

SIGN

SIN

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

Exasol supports the following string functions:

ASCII

BIT_LENGTH

CHARACTER_LENGTH

CH[A]R

COLOGNE_PHONETIC

CONCAT

DUMP

EDIT_DISTANCE

INITCAP

INSERT

INSTR

LCASE

LEFT

LENGTH

LOCATE

LOWER

LPAD

LTRIM

MID

OCTET_LENGTH

POSITION

REGEXP_INSTR

REGEXP_REPLACE

REGEXP_SUBSTR

REPEAT

REPLACE

REVERSE

RIGHT

RPAD

RTRIM

SOUNDEX

SPACE

SUBSTR[ING]

TO_CHAR (datetime)

TO_CHAR (number)

TO_NUMBER

TRANSLATE

TRIM

UCASE

UNICODE

UNICODECHR

UPPER

Date/Time Functions

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

Exasol supports the following date/time functions:

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

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

CASE

COALESCE

CURRENT_SCHEMA

CURRENT_SESSION

CURRENT_STATEMENT

CURRENT_USER

DECODE

GREATEST

HASH_MD5

HASH_SHA[1]

HASH_SHA256

HASH_SHA512

HASH_TIGER

HASHTYPE_MD5

HASHTYPE_SHA[1]

HASHTYPE_SHA256

HASHTYPE_SHA512

HASHTYPE_TIGER

IPROC

LEAST

NULLIF

NULLIFZERO

NPROC

NVL

NVL2

ROWNUM

ROWID

SCOPE_USER

SESSION_PARAMETER

SYS_GUID

USER

VALUE2PROC

ZEROIFNULL