BizTalk Adapter for Cosmos DB

Build 21.0.7930

SQL API Built-In Functions

Cosmos DB also supports a number of built-in functions for common operations, that can be used inside queries. Here are some example of how can be used as part of select columns or the WHERE clause:

Use Built-in functions as part of SELECT columns

SELECT IS_NUMBER(user_id) AS ISN_ATTR, IS_NUMBER(id) AS ISN_ID FROM [users]
SELECT POWER(user_id, 2) AS POWERSSS, LENGTH(id) AS LENGTH_ID, PI() AS JustThePI FROM [users]

Use Built-in functions as part of WHERE clause

SELECT * FROM [users] WHERE STARTSWITH(middle_name, 'G')
SELECT * FROM [users] WHERE REPLACE(middle_name, 'Chr', '___') = '___istopher'

Function groupOperations
Mathematical functionsABS, CEILING, EXP, FLOOR, LOG, LOG10, POWER, ROUND, SIGN, SQRT, SQUARE, TRUNC, ACOS, ASIN, ATAN, ATN2, COS, COT, DEGREES, PI, RADIANS, SIN, and TAN
Type checking functionsIS_ARRAY, IS_BOOL, IS_NULL, IS_NUMBER, IS_OBJECT, IS_STRING, IS_DEFINED, and IS_PRIMITIVE
String functionsCONCAT, CONTAINS, ENDSWITH, INDEX_OF, LEFT, LENGTH, LOWER, LTRIM, REPLACE, REPLICATE, REVERSE, RIGHT, RTRIM, STARTSWITH, SUBSTRING, and UPPER
Array functionsARRAY_CONCAT, ARRAY_CONTAINS, ARRAY_LENGTH, and ARRAY_SLICE

Mathematical functions

The mathematical functions each perform a calculation, based on input values that are provided as arguments, and return a numeric value. Here's a table of supported built-in mathematical functions.

UsageDescription
ABS (num_expr) Returns the absolute (positive) value of the specified numeric expression.
CEILING (num_expr) Returns the smallest integer value greater than, or equal to, the specified numeric expression.
FLOOR (num_expr) Returns the largest integer less than or equal to the specified numeric expression.
EXP (num_expr) Returns the exponent of the specified numeric expression.
LOG (num_expr [,base]) Returns the natural logarithm of the specified numeric expression, or the logarithm using the specified base
LOG10 (num_expr) Returns the base-10 logarithmic value of the specified numeric expression.
ROUND (num_expr) Returns a numeric value, rounded to the closest integer value.
TRUNC (num_expr) Returns a numeric value, truncated to the closest integer value.
SQRT (num_expr) Returns the square root of the specified numeric expression.
SQUARE (num_expr) Returns the square of the specified numeric expression.
POWER (num_expr, num_expr) Returns the power of the specified numeric expression to the value specified.
SIGN (num_expr) Returns the sign value (-1, 0, 1) of the specified numeric expression.
ACOS (num_expr) Returns the angle, in radians, whose cosine is the specified numeric expression; also called arccosine.
ASIN (num_expr) Returns the angle, in radians, whose sine is the specified numeric expression. This is also called arcsine.
ATAN (num_expr) Returns the angle, in radians, whose tangent is the specified numeric expression. This is also called arctangent.
ATN2 (num_expr) Returns the angle, in radians, between the positive x-axis and the ray from the origin to the point (y, x), where x and y are the values of the two specified float expressions.
COS (num_expr) Returns the trigonometric cosine of the specified angle, in radians, in the specified expression.
COT (num_expr) Returns the trigonometric cotangent of the specified angle, in radians, in the specified numeric expression.
DEGREES (num_expr) Returns the corresponding angle in degrees for an angle specified in radians.
PI () Returns the constant value of PI.
RADIANS (num_expr) Returns radians when a numeric expression, in degrees, is entered.
SIN (num_expr) Returns the trigonometric sine of the specified angle, in radians, in the specified expression.
TAN (num_expr) Returns the tangent of the input expression, in the specified expression.

Type checking functions

The type checking functions allow you to check the type of an expression within SQL queries. Type checking functions can be used to determine the type of properties within documents on the fly when it is variable or unknown. Here's a table of supported built-in type checking functions.

UsageDescription
IS_ARRAY (expr) Returns a Boolean indicating if the type of the value is an array.
IS_BOOL (expr) Returns a Boolean indicating if the type of the value is a Boolean.
IS_NULL (expr) Returns a Boolean indicating if the type of the value is null.
IS_NUMBER (expr) Returns a Boolean indicating if the type of the value is a number.
IS_OBJECT (expr) Returns a Boolean indicating if the type of the value is a JSON object.
IS_STRING (expr) Returns a Boolean indicating if the type of the value is a string.
IS_DEFINED (expr) Returns a Boolean indicating if the property has been assigned a value.
IS_PRIMITIVE (expr) Returns a Boolean indicating if the type of the value is a string, number, Boolean or null.

String functions

The following scalar functions perform an operation on a string input value and return a string, numeric or Boolean value. Here's a table of built-in string functions:

UsageDescription
LENGTH (str_expr) Returns the number of characters of the specified string expression
CONCAT (str_expr, str_expr [, str_expr]) Returns a string that is the result of concatenating two or more string values.
SUBSTRING (str_expr, num_expr, num_expr) Returns part of a string expression.
STARTSWITH (str_expr, str_expr) Returns a Boolean indicating whether the first string expression starts with the second
ENDSWITH (str_expr, str_expr) Returns a Boolean indicating whether the first string expression ends with the second
CONTAINS (str_expr, str_expr) Returns a Boolean indicating whether the first string expression contains the second.
INDEX_OF (str_expr, str_expr) Returns the starting position of the first occurrence of the second string expression within the first specified string expression, or -1 if the string is not found.
LEFT (str_expr, num_expr) Returns the left part of a string with the specified number of characters.
RIGHT (str_expr, num_expr) Returns the right part of a string with the specified number of characters.
LTRIM (str_expr) Returns a string expression after it removes leading blanks.
RTRIM (str_expr) Returns a string expression after truncating all trailing blanks.
LOWER (str_expr) Returns a string expression after converting uppercase character data to lowercase.
UPPER (str_expr) Returns a string expression after converting lowercase character data to uppercase.
REPLACE (str_expr, str_expr, str_expr) Replaces all occurrences of a specified string value with another string value.
REPLICATE (str_expr, num_expr) Repeats a string value a specified number of times.
REVERSE (str_expr) Returns the reverse order of a string value.

Array functions

The following scalar functions perform an operation on an array input value and return numeric, Boolean or array value. Here's a table of built-in array functions:

UsageDescription
ARRAY_LENGTH (arr_expr) Returns the number of elements of the specified array expression.
ARRAY_CONCAT (arr_expr, arr_expr [, arr_expr]) Returns an array that is the result of concatenating two or more array values.
ARRAY_CONTAINS (arr_expr, expr [, bool_expr]) Returns a Boolean indicating whether the array contains the specified value. Can specify if the match is full or partial.
ARRAY_SLICE (arr_expr, num_expr [, num_expr]) Returns part of an array expression.

Nested functions

You can also perform nested built-in functions, wich will be processed server side as well:

i.e. SELECT TOP 10 CONCAT(SUBSTRING(UPPER(cuisine), 0, 3), '-cuisine') FROM [restaurants]

Copyright (c) 2021 CData Software, Inc. - All rights reserved.
Build 21.0.7930