SQL Functions

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

Built-In 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

Conditional Functions

Function	Description
CASE	Test a conditional expression, and depending on the result, evaluate additional expressions.
COALESCE	Evaluate first non-NULL expression
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 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
CHR	Returns the ASCII character representation of the supplied integer
IS CASTABLE	Checks whether a cast operation is possible or supported for a given column and data type and provides an alternative when there is an exception

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
Date and Time TRUNC	Truncates a date element down to a specified date or time element

Numeric Functions

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

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
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
SIGN	Returns a value of -1, 0, or 1, depending on the sign of the input argument
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 Functions

Function	Description
CHARACTER_LENGTH / CHAR_LENGTH	Calculates number of characters in an argument
CHARINDEX	Calculates the position where a string starts inside another string
|| (Concatenate)	Concatenates two strings
CRC64	Calculates a CRC-64 hash of an argument
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
SELECT ASCII	Returns an INT value representing the ASCII code of the leftmost character in a string

User-Defined Scalar Functions

Function
Scalar SQL UDF
Simple Scalar SQL UDFs

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
PERCENTILE CONT		Returns the value located in the selected percentile of a specified column
PERCENTILE DISC		Returns an exact value located in the selected percentile of a specified column based on the closest values in your data to the requested percentile
STDDEV_POP	stdevp	Calculates population standard deviation of values
STDDEV_SAMP	stdev, stddev	Calculates sample standard deviation of values
SUM		Calculates the sum of all of the values or only distinct values
VAR_POP	varp	Calculates population variance of values
VAR_SAMP	var, variance	Calculates sample variance of values

Window Functions

The following table shows the window functions:

Function	Description
CUME_DIST	Returns the cumulative distribution of rows
DENSE_RANK	Returns the rank of the current row with no gaps
FIRST_VALUE	Returns the value in the first row of a window
LAG	Calculates the value evaluated at the row that is before the current row within the partition
LAST_VALUE	Returns the value in the last row of a window
LEAD	Calculates the value evaluated at the row that is after the current row within the partition
NTH_VALUE	Returns the value in a specified (n) row of a window
NTILE	Returns an integer ranging between 1 and the argument value, dividing the partitions as equally as possible
PERCENT_RANK	Returns the relative rank of the current row
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
ROW_NUMBER	Returns the row number of each row within the partition of a result set