SQL functions¶

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

Summary of functions¶

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 an `NVARCHAR` for ASCII-only characters
IS NULL	Check for `NULL` [ or non-`NULL` ] values

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

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
DATEADD	Adds an interval to 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

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 (e^x)
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 (x^y)
RADIANS	Converts a value from degree values to radians
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 (x^y)
TAN	Calculates the tangent of an argument
TRUNC	Rounds a number to its integer representation towards 0

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
LOWER	Converts an argument to an upper-case equivalent

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

Function	Description
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 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

Function	Description
SUBSCRIBE_SERVICE	Add a SQream DB worker to a service queue
UNSUBSCRIBE_SERVICE	Remove a SQream DB worker to a service queue
SHOW_SUBSCRIBED_INSTANCES	Return a list of service queues and workers