# SQL functions¶

SQream DB supports functions from ANSI SQL, as well as others for compatibility.

## Summary of functions¶

### Scalar functions¶

#### Bitwise operations¶

Function Description
& (bitwise AND) Bitwise AND
~ (bitwise NOT) Bitwise NOT
| (bitwise OR) Bitwise OR
<< (bitwise shift left) Bitwise shift left
>> (bitwise shift right) Bitwise shift right
XOR (bitwise XOR) Bitwise XOR

#### Conditionals¶

Function Description
BETWEEN Value is in [ or not within ] the range
CASE Test a conditional expression, and depending on the result, evaluate additional expressions.
COALESCE Evaluate first non-NULL expression
IN Value is in [ or not within ] a set of values
ISNULL Alias for COALESCE with two expressions
IS_ASCII Test a TEXT for ASCII-only characters
IS NULL Check for NULL [ or non-NULL ] values

#### Conversion¶

Function Description
FROM_UNIXTS, FROM_UNIXTSMS Converts a UNIX Timestamp to DATE or DATETIME
TO_HEX Converts a number to a hexadecimal string representation
TO_UNIXTS, TO_UNIXTSMS Converts a DATE or DATETIME to a UNIX Timestamp

#### Date and time¶

Function Description
CURDATE Special syntax, equivalent to CURRENT_DATE
CURRENT_DATE Returns the current date as DATE
CURRENT_TIMESTAMP Equivalent to GETDATE
DATEPART Extracts a date or time element from a date expression
DATEDIFF Calculates the time difference between two date expressions
EOMONTH Calculates the last day of the month of a given date expression
EXTRACT ANSI syntax for extracting date or time element from a date expression
GETDATE Returns the current timestamp as DATETIME
SYSDATE Equivalent to GETDATE
TRUNC Truncates a date element down to a specified date or time element

#### Numeric¶

Arithmetic operators
Operator Syntax Description
+ (unary) +a Converts a string to a numeric value. Identical to a :: double
+ a + b Adds two expressions together
- (unary) -a Negates a numeric expression
- a - b Subtracts b from a
* a * b Multiplies a by b
/ a / b Divides a by b
% a % b Modulu of a by b. See also MOD, %
Functions
Function Description
ABS Calculates the absolute value of an argument
ACOS Calculates the inverse cosine of an argument
ASIN Calculates the inverse sine of an argument
ATAN Calculates the inverse tangent of an argument
ATN2 Calculates the inverse tangent for a point (y, x)
CEILING / CEIL Calculates the next integer for an argument
COS Calculates the cosine of an argument
COT Calculates the cotangent of an argument
CRC64 Calculates a CRC-64 hash of an argument
DEGREES Converts a value from radian values to degrees
EXP Calcalates the natural exponent for an argument (ex)
FLOOR Calculates the largest integer smaller than the argument
LOG Calculates the natural log for an argument
LOG10 Calculates the 10-based log for an argument
MOD, % Calculates the modulu (remainder) of two arguments
PI Returns the constant value for π
POWER Calculates x to the power of y (xy)
ROUND Rounds an argument down to the nearest integer, or an arbitrary precision
SIN Calculates the sine of an argument
SQRT Calculates the square root of an argument (√x)
SQUARE Raises an argument to the power of 2 (xy)
TAN Calculates the tangent of an argument
TRUNC Rounds a number to its integer representation towards 0

#### Strings¶

Function Description
CHAR_LENGTH Calculates number of characters in an argument
CHARINDEX Calculates the position where a string starts inside another string
|| (Concatenate) Concatenates two strings
ISPREFIXOF Matches if a string is the prefix of another string
LEFT Returns the first number of characters from an argument
LEN Calculates the length of a string in characters
LIKE Tests if a string argument matches a pattern
LOWER Converts an argument to a lower-case equivalent
LTRIM Trims whitespaces from the left side of an argument
OCTET_LENGTH Calculates the length of a string in bytes
PATINDEX Calculates the position where a pattern matches a string
REGEXP_COUNT Calculates the number of matches of a regular expression match in an argument
REGEXP_INSTR Returns the start position of a regular expression match in an argument
REGEXP_SUBSTR Returns a substring of an argument that matches a regular expression
REPLACE Replaces characters in a string
REVERSE Reverses a string argument
RIGHT Returns the last number of characters from an argument
RLIKE Tests if a string argument matches a regular expression pattern
RTRIM Trims whitespace from the right side of an argument
SUBSTRING Returns a substring of an argument
TRIM Trims whitespaces from an argument
UPPER Converts an argument to an upper-case equivalent

### Aggregate functions¶

Function Aliases Description
AVG   Calculates the average of all of the values
CORR   Calculates the Pearson correlation coefficient
COUNT   Calculates the count of all of the values or only distinct values
COVAR_POP   Calculates population covariance of values
COVAR_SAMP   Calculates sample covariance of values
MAX   Returns maximum value of all values
MIN   Returns minimum value of all values
SUM   Calculates the sum of all of the values or only distinct values
STDDEV_SAMP stdev, stddev Calculates sample standard deviation of values
STDDEV_POP stdevp Calculates population standard deviation of values
VAR_SAMP var, variance Calculates sample variance of values
VAR_POP varp Calculates population variance of values

### Window functions¶

Function Description
LAG Calculates the value evaluated at the row that is before the current row within the partition
LEAD Calculates the value evaluated at the row that is after the current row within the partition
MAX Calculates the maximum value
MIN Calculates the minimum value
RANK Calculates the rank of a row
ROW_NUMBER Calculates the row number
SUM Calculates the sum of all of the values

### System functions¶

System functions allow you to execute actions in the system, such as aborting a query or get information about system processes.

Function Description
EXPLAIN Returns a static query plan for a statement
SHOW_CONNECTIONS Returns a list of jobs and statements on the current worker
SHOW_LOCKS Returns any existing locks in the database
SHOW_NODE_INFO Returns a query plan for an actively running statement with timing information
SHOW_SERVER_STATUS Shows running statements across the cluster
SHOW_VERSION Returns the version of SQream DB
STOP_STATEMENT Stops a query (or statement) if it is currently running