SQL Functions

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

Summary of Functions

Built-In Scalar Functions

For more information about built-in scalar functions, see Built-In Scalar functions.

Bitwise Operations

The following table shows the bitwise operations functions:

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

The following table shows the conditionals 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 a TEXT for ASCII-only characters
IS NULL	Check for NULL [ or non-NULL ] values

Conversion

The following table shows the conversion functions:

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

The following table shows the date and time functions:

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

Numeric

The following table shows the arithmetic operators

For more information about arithmetic operator, see Arithmetic operators.

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, %

The following table shows the arithmetic operator functions:

Arithemtic Operator 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)
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 (xy)
TAN	Calculates the tangent of an argument
TRUNC	Rounds a number to its integer representation towards 0

Strings

The following table shows the **string* functions:

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_REPLACE	Replaces and returns the text column substrings of a regular expression match in an argument
REGEXP_SUBSTR	Returns a substring of an argument that matches a regular expression
REPEAT	Repeats a string as many times as specified
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

User-Defined Scalar Functions

For more information about user-defined scalar functions, see Scalar SQL UDF

Aggregate Functions

The following table shows the aggregate functions:

For more information about aggregate functions, see 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

The following table shows the window functions:

For more information about window functions, see 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
SUM	Calculates the sum of all of the values
RANK	Calculates the rank of a row
FIRST_VALUE	Returns the value in the first row of a window
LAST_VALUE	Returns the value in the last row of a window
NTH_VALUE	Returns the value in a specified (n) row of a window
DENSE_RANK	Returns the rank of the current row with no gaps
PERCENT_RANK	Returns the relative rank of the current row
CUME_DIST	Returns the cumulative distribution of rows
NTILE	Returns an integer ranging between 1 and the argument value, dividing the partitions as equally as possible

System Functions

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

The following table shows the system functions:

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

Workload Management Functions

The following table shows the workload management functions:

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