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_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 systemwide 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 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 leftaligned 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 leastsquare 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 rightaligned 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 socalled 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 153, specified in ISO8601 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. 