Alphabetical List of Functions

This section provides a list of all the functions available in Exasol, listed alphabetically. Click the function name to view its details.

Function Name Purpose
ABS Returns the absolute sum of numbers n.
ACOS Returns the arccosine of number n. The result is between 0 and π.
ADD_DAYS Adds a specified number of days to a date or timestamp.
ADD_HOURS Adds a specified number of hours to a timestamp.
ADD_MINUTES Adds a specified number of minutes to a timestamp.
ADD_MONTHS Adds a specified number of months to a date or timestamp.
ADD_SECONDS Adds a specified number of seconds to a timestamp.
ADD_WEEKS Adds a specified number of weeks to a date or timestamp.
ADD_YEARS Adds a specified number of years to a date or timestamp.
ANY Returns TRUE if the value expr is true for at least one row in the window or group of input rows. Otherwise, this function returns FALSE.
APPROXIMATE_COUNT_DISTINCT Returns the approximate number of distinct elements (without NULL).
ASCII Returns the numeric value of character in the ASCII character set.
ASIN Returns the arcsine of numbers n. The result is between -π/2 and π/2.
ATAN Returns the arctangent of number n. The result is between -π/2 and π/2.
ATAN2 Returns the arctangent of two numbers n and m. The expression is equivalent to ATAN(n/m)
AVG Returns the mean value
BIT_AND Computes the bitwise AND operation of two numerical values.
BIT_CHECK Checks whether a certain bit of a numerical value is set. The position parameter starts from 0, which means the lowest bit.
BIT_LENGTH Returns the bit length of a string. If only ASCII characters are used, then this function is equivalent to CHARACTER_ LENGTH * 8.
BIT_LROTATE Rotates the bits of a number by the specified number to the left.
BIT_LSHIFT Shifts the bits of a number by the specified number to the left.
BIT_NOT Computes the bitwise negation of a single numerical value.
BIT_OR Computes the bitwise OR operation of two numerical values.
BIT_RROTATE Rotates the bits of a number by the specified number to the right.
BIT_RSHIFT Shifts the bits of a number by the specified number to the right.
BIT_SET Sets a certain bit of a numerical value. The position parameter starts from 0, which is the lowest bit.
BIT_TO_NUM Creates a numerical value out of a list of single bits.
BIT_XOR Computes the bitwise exclusive OR operation of two numerical values. The result in each position is , if the two corresponding bits are different.
CASE With the help of the CASE function, an IF THEN ELSE logic can be expressed within the SQL language.
CAST Converts an expression into the specified data type.
CEIL[ING] Returns the smallest whole number that is larger or equal to the given number.
CH[A]R Returns the ASCII character whose ordinal number is the given integer
CHARACTER_LENGTH Returns the length of a string in characters.
COALESCE Returns the first value from the argument list which is not NULL.If all of the values are NULL, the function returns NULL
COLOGNE_PHONETIC Returns a phonetic representation of a string.
CONCAT Returns the concatenation of a number of strings.
CONNECT_BY_ISCYCLE Returns whether a row causes a cycle for a CONNECT BY query.
CONNECT_BY_ISLEAF Returns whether a row is a leaf within the tree for a CONNECT BY query.
CONVERT Converts an expression into the specified data type if this is possible.
CONVERT_TZ Converts a timestamp from one time zone into another one.
CORR Returns the coefficient of correlation of a set of number pairs (a type of relation measure).
COS Returns the cosine of number n.
COSH Returns the hyperbolic cosine of number n.
COT Returns the cotangent of number n.
COUNT Returns the number of rows in the result set of a SQL query.
COVAR_POP Returns the population covariance of a set of number pairs (a type of relation measure).
COVAR_SAMP Returns the sample covariance of a set of number pairs (a type of relation measure).
CUME_DIST Calculates the cumulative distribution of a value in a group of values.
CURDATE Returns the current date by evaluating TO_DATE(CURRENT_TIMESTAMP).
CURRENT_DATE Returns the current date by evaluating TO_DATE(CURRENT_TIMESTAMP).
CURRENT_SCHEMA Returns the schema that is currently open. If a schema is not open, a NULL value is returned.
CURRENT_SESSION Returns the id of the current session.
CURRENT_STATEMENT Returns the current id of the statements which is serially numbered within the current session.
CURRENT_TIMESTAMP Returns the current timestamp, interpreted in the current session time zone.
CURRENT_USER Returns the current user.
DATE_TRUNC This is a PostgreSQL compatible function to round down date and timestamp values.
DAY Returns the day of a date.
DAYS_BETWEEN Returns the number of days between two date values.
DBTIMEZONE Returns the database time zone which is set system-wide in EXAoperation and represents the local time zone of the Exasol servers.
DECODE Returns the result value for which the expression, expr, matches the expression, search. If no match is found, NULL or − if specified − the default value is returned.
DEGREES Returns the corresponding angle in degrees for an angle specified in radians.
DENSE_RANK Returns the rank of a row within an ordered partition.
DIV Returns the integer quotient of m and n.
DUMP Returns the byte length and the character set of string, as well as the internal representation of the characters specified by start position start and length as length.
EDIT_DISTANCE Defines the distance between two strings, indicating their similarity.
EVERY Returns TRUE if the value of expr is true for all rows in the window or group of input rows. Otherwise, this function returns FALSE.
EXP Returns the number e (Euler's number) to the power of n.
EXTRACT Extracts specific values from a timestamp, date or interval.
FIRST_VALUE Returns the result of expr from the first row in the window.
FLOOR Returns the largest whole number that is smaller or equal to n
FROM_POSIX_TIME Converts the Posix Time (that means a numerical value) to a timestamp.
GREATEST Returns the largest of the specified expressions.
GROUP_CONCAT Returns a concatenated string of values of expr from all rows in the window or group.
GROUPING[_ID] Distinguishes between regular result rows and superaggregate rows which are created in case of GROUPING SETS, CUBE or ROLLUP clauses.
HASH_MD5 Computes a hash value by using the MD5 algorithm (128 Bit).
HASH_SHA[1] Computes a hash value by using the SHA1 algorithm (160 Bit).
HASH_SHA256 Computes a hash value by using the SHA256 algorithm (256 Bit).
HASH_SHA512 Computes a hash value by using the SHA512 algorithm (512 Bit).
HASH_TIGER Computes a hash value by using the tiger algorithm (192 Bit).
HASHTYPE_MD5 Computes a hash value by using the MD5 algorithm (128 Bit).
HASHTYPE_SHA[1] Computes a hash value by using the SHA1 algorithm (160 Bit).
HASHTYPE_SHA256 Computes a hash value by using the SHA256 algorithm (256 Bit).
HASHTYPE_SHA512 Computes a hash value by using the SHA512 algorithm (512 Bit).
HASHTYPE_TIGER Computes a hash value by using the tiger algorithm (192 Bit).
HOUR Returns the hours of a timestamp.
HOURS_BETWEEN Returns the number of hours between timestamp timestamp1 and timestamp timestamp2
IF The IF THEN ELSE syntax is an alternate to CASE function.
INITCAP Returns the specified string, with the first letter of each word in uppercase, all other letters is lowercase.
INSERT Replaces the substring of string, with length length beginning at position, with string new_string
INSTR Returns the position in string at which search_string appears. If this is not contained, the value 0 is returned.
IPROC Returns the local node number within the cluster.
IS_* Returns TRUE if string can be converted to a certain data type.
JSON_EXTRACT Extracts one or more SQL values from a valid JSON string.
JSON_VALUE Extracts a single SQL value at a given path from a valid JSON string.
LAG Computes the result of expr on the row that is precisely offset rows prior to the current row in the partition.
LAST_VALUE Returns the result of expr from the first row in the window.
LCASE Converts the specified string into lower case letters.
LEAD Computes the result of expr on the row that is precisely offset rows following current row in the partition.
LEAST Returns the smallest of the specified expressions.
LEFT Returns the left-aligned substring of string with length length
LENGTH Returns the length of a string in characters.
LEVEL Returns for CONNECT BY queries the level of a node within the tree.
LISTAGG Returns a concatenated string of values of expr from all rows in the window or group.
LN Returns the natural logarithm of number n. The function LN(n) is equivalent to LOG(EXP(1),n)
LOCALTIMESTAMP Returns the current timestamp, interpreted in the current session time zone.
LOCATE Returns the position in string at which search_string appears. If this is not contained, the value 0 is returned.
LOG Returns the logarithm of n with base base
LOG2 Returns the logarithm of n with base 2.
LOG10 Returns the logarithm of n with base 10.
LOWER Converts the specified string into lower case letters.
LPAD Returns a string of length n, which is string, filled from the left with expression padding
LTRIM Deletes all of the characters specified in the expression trim_chars from the left border of string.
MAX Returns the maximum value of expr from the window or group of rows.
MEDIAN Returns the middle value or an interpolated value which would be the middle value once the elements are sorted (NULL values are ignored).
MID Returns a substring of length length from position position out of the string string.
MIN Returns the minimum value of expr from the window or group of rows.
MINUTE Returns the minutes of a timestamp.
MINUTES_BETWEEN Returns the number of minutes between two timestamps timestamp1 and timestamp2.
MIN_SCALE This function returns the minimum scale needed to represent the value exactly.
MOD Returns the remainder of the division of m by n.
MONTH Returns the month of a date.
MONTHS_BETWEEN Returns the number of months between two date values.
MUL Returns the product of expr within a window or group of rows.
NOW Returns the current timestamp, interpreted in the current session time zone.
NPROC Returns the number of database nodes in the cluster.
NTH_VALUE Returns the value of expr of the nth row in the window specified by the over_clause
NTILE Distributes the rows of an ordered partition into a specified number of buckets. For each row, this function returns the index of the bucket to which the row belongs.
NULLIF Returns the value NULL, if two expressions are identical. Otherwise, the first expression is returned.
NULLIFZERO Returns the value NULL if number has value 0. Otherwise, a number is returned.
NUMTODSINTERVAL Converts a numerical value n into an interval of type INTERVAL DAY TO SECOND.
NUMTOYMINTERVAL Converts a numerical value n into an interval of type INTERVAL YEAR TO MONTH.
NVL Replaces NULL values with the expression, expr2.
NVL2 Replaces NULL values with expr3, otherwise it uses expr2.
OCTET_LENGTH Returns the octet length of a string.
PERCENT_RANK Computes the relative rank of each row in the partition.
PERCENTILE_CONT This is an inverse distribution function and expects as an input parameter a percentile value and a sorting specification which defines the rank of each element within a group. Returns the percentile of the sort order.
PERCENTILE_DISC Returns the value from the group set which has the smallest cumulative distribution value (corresponding to the given sort order), which is larger than or equal to the specified percentile value.
PI Returns the value of the mathematical constant π (pi).
POSITION Returns the position in the string, string, at which the string, search_string first appears.
POSIX_TIME Convert a datetime value to a numerical value.
POWER Returns the power of two numbers.
RADIANS Converts the number n from degrees to radians.
RAND[OM] Generates a random number.
RANK Computes the rank for each row by adding 1 (one) to the number of rows that precede the current row and are not peers of the current row.
RATIO_TO_REPORT Computes the ratio of a value to the overall sum.
REGEXP_INSTR Searches the regular expression pattern in the string. If this is not contained, the value 0 is returned, otherwise the corresponding position of the match.
REGEXP_REPLACE Replaces the occurrences of pattern in a string by replace_string.
REGEXP_SUBSTR Returns a substring of the parameter string
REGR_FUNCTIONS With the help of the linear regression functions you can determine a least-square regression line.
REPEAT Returns the concatenation of n copies of a string.
REPLACE Returns the string that emerges if in the string all occurrences of search_string are replaced by replace_ string.
REVERSE Returns the reverse of a string value.
RIGHT Returns the right-aligned substring of string with length length.
ROUND (datetime) Rounds a date or timestamp value to the specified unit.
ROUND (number) Rounds number n to integer digits behind the decimal point (round to nearest, in case of a tie away of the zero).
ROWNUM This is a pseudo column which numbers the records of a table or subselect , beginning with 1. Has certain restrictions on usage.
ROW_NUMBER Returns the number of a row in an ordered partition.
ROWID Every row of a base table in the database has a unique address, the so-called ROWID. Read access to this address can be obtained through the ROWID pseudo column (DECIMAL(36.0) data type).
RPAD Returns a string of the length n, which is string, filled from the right with expression padding.
RTRIM Deletes all of the characters specified in the expression trim_chars from the right border of string.
SCOPE_USER Returns the current scope user within a query, which is either identical to CURRENT_USER or the owner of the view or the virtual table if the function refers to one.
SECOND Returns the seconds of a timestamp
SECONDS_BETWEEN Returns the number of seconds between two timestamps.
SESSION_PARAMETER Returns the session parameter value of a given session and a parameter name.
SESSIONTIMEZONE Returns the session time zone which was set using ALTER SESSION.
SIGN Returns the signum of number n as one of -1, 0, 1.
SIN Returns the sine of number n
SINH Returns the hyperbolic sine of number n
SOME

This is an alias for ANY.

SOUNDEX Returns a phonetic representation of a string
SPACE Creates a string consisting of n spaces.
SQRT Returns the square root of number n.
ST_* This functions is used for geospatial objects.
STDDEV Returns the standard deviation within a window or group of rows.
STDDEV_POP Returns the standard deviation of expr within a window or group of rows.
STDDEV_SAMP Returns the standard deviation of expr within a window or group of rows.
SUBSTR[ING] Returns a substring of the length length from the position position, out of the string string.
SUM returns the sum of expr within a window or group of rows.
SYS_CONNECT_BY_PATH Returns a string containing the full path from the root node to the current node.
SYS_GUID Returns a system wide unique hexadecimal of type CHAR(48).
SYSDATE Returns the current system date by evaluating TO_DATE(SYSTIMESTAMP), interpreted in the current database time zone.
SYSTIMESTAMP Returns the current timestamp, interpreted in the current database time zone.
TAN Returns the tangent of number n.
TANH Returns the hyperbolic tangent of number n.
TO_CHAR (datetime) Converts a date, timestamp or interval into a string.
TO_CHAR (number) Converts a number into a string.
TO_DATE Converts a string into a date.
TO_DSINTERVAL Converts a string value into an interval (INTERVAL DAY TO SECOND).
TO_NUMBER Converts a string into a number.
TO_TIMESTAMP Converts the string into a timestamp.
TO_YMINTERVAL Converts a string value into an interval (INTERVAL YEAR TO MONTH).
TRANSLATE Replaces the characters out of from_string with the corresponding character out of to_string in the string, expr.
TRIM Deletes all of the characters specified in the expression, trim_string, from both the right and left border of string.
TRUNC[ATE] (datetime) Returns a date and/or a timestamp, which is trimmed following the format definition.
TRUNC[ATE] (number) Trims number n to integer places behind the decimal point.
TYPEOF Returns the type of the SQL expression.
UCASE Converts the specified string into upper case letters.
UNICODE Returns the numeric unicode value of a character.
UNICODECHR Returns the unicode character which equates to the numeric value n
UPPER Converts the specified string into upper case letters.
USER Returns the current user.
VALUE2PROC Returns the corresponding database node for a certain value.
VAR_POP Returns the variance of expr within a window or group of rows.
VAR_SAMP Returns the variance of expr within a window or group of rows.
VARIANCE Returns the variance of expr within a window or group of rows.
WEEK Returns the week of a date (values 1-53, specified in ISO-8601 standard).
YEAR Returns the year of a date.
YEARS_BETWEEN Returns the number of years between two date values.
ZEROIFNULL Returns 0 if number has value NULL. Otherwise, number is returned.