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:

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

For more information about arithmetic operators, see Arithmetic operators.

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

decode

Decodes or extracts binary data from a textual input string

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:

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

For more information about aggregate functions, see Aggregate Functions.

Window Functions

The following table shows the 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

For more information about window functions, see Window Functions.

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