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_CLUSTER | Returns the name of the database cluster that on which the current session is running. |
| 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 |
| 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 lowercase 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 lowercase 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 uppercase 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 uppercase 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. |
