API Query functions

Below is a list of all functions available to the Matillion ETL API Query component (as accessed through 'Advanced Mode'). The format below is the functions name and expected arguments, then a brief explanation of its purpose, then a brief explanation of the arguments.

String Functions

ASCII(character_expression)

Returns the ASCII code value of the left-most character of the character expression.

character_expression: The character expression.

CHAR(integer_expression)

Converts the integer ASCII code to the corresponding character.

integer_expression: The integer from 0 through 255.

CHARINDEX(expressionToFind ,expressionToSearch [,start_location ])

Returns the starting position of the specified expression in the character string.

expressionToFind: The character expression to find.
expressionToSearch: The character expression, typically a column, to search.
start_location: The optional character position to start searching for expressionToFind in expressionToSearch.

CONCAT(string_value1, string_value2 [, string_valueN])

Returns the string that is the concatenation of two or more string values.

string_value1: The first string to be concatenated.
string_value2: The second string to be concatenated.
*: The optional additional strings to be concatenated.

CONTAINS(expressionToSearch, expressionToFind)

Returns 1 if expressionToFind is found within expressionToSearch; otherwise, 0.

expressionToSearch: The character expression, typically a column, to search.
expressionToFind: The character expression to find.

ENDSWITH(character_expression, character_suffix)

Returns 1 if character_expression ends with character_suffix; otherwise, 0.

character_expression: The character expression.
character_suffix: The character suffix to search for.

FORMAT(value, format)

Returns the value formatted with the specified format.

value: The string to format.
format: The string specifying the syntax of the date or numeric format.

INDEXOF(expressionToSearch, expressionToFind [,start_location ])

Returns the starting position of the specified expression in the character string.

expressionToSearch: The character expression, typically a column, to search.
expressionToFind: The character expression to find.
start_location: The optional character position to start searching for expressionToFind in expressionToSearch.

ISNULL ( check_expression , replacement_value )

Replaces null with the specified replacement value.

check_expression: The expression to be checked for null.
replacement_value: The expression to be returned if check_expression is null.

JSON_AVG(json, jsonpath)

Computes the average value of a JSON array within a JSON object. The path to the array is specified in the jsonpath argument. Return value is numeric or null.

json: The JSON document to compute.
jsonpath: The JSONPath used to select the nodes. [x], [2..], [..8], or [1..12] are accepted. [x] selects all nodes.

JSON_COUNT(json, jsonpath)

Returns the number of elements in a JSON array within a JSON object. The path to the array is specified in the jsonpath argument. Return value is numeric or null.

json: The JSON document to compute.
jsonpath: The JSONPath used to select the nodes. [x], [2..], [..8], or [1..12] are accepted. [x] selects all nodes.

JSON_EXTRACT(json, jsonpath)

Selects any value in a JSON array or object. The path to the array is specified in the jsonpath argument. Return value is numeric or null.

json: The JSON document to extract.
jsonpath: The XPath used to select the nodes. The JSONPath must be a string constant. The values of the nodes selected will be returned in a token-separated list.

JSON_MAX(json, jsonpath)

Gets the maximum value in a JSON array within a JSON object. The path to the array is specified in the jsonpath argument. Return value is numeric or null.

json: The JSON document to compute.
jsonpath: The JSONPath used to select the nodes. [x], [2..], [..8], or [1..12] are accepted. [x] selects all nodes.

JSON_MIN(json, jsonpath)

Gets the minimum value in a JSON array within a JSON object. The path to the array is specified in the jsonpath argument. Return value is numeric or null.

json: The JSON document to compute.
jsonpath: The JSONPath used to select the nodes. [x], [2..], [..8], or [1..12] are accepted. [x] selects all nodes.

JSON_SUM(json, jsonpath)

Computes the summary value in JSON according to the JSONPath expression. Return value is numeric or null.

json: The JSON document to compute.
jsonpath: The JSONPath used to select the nodes. [x], [2..], [..8], or [1..12] are accepted. [x] selects all nodes.

LEFT ( character_expression , integer_expression )

Returns the specified number of characters counting from the left of the specified string.

character_expression: The character expression.
integer_expression: The positive integer that specifies how many characters will be returned counting from the left of character_expression.

LEN(string_expression)

Returns the number of characters of the specified string expression.

string_expression: The string expression.

LOWER ( character_expression )

Returns the character expression with the uppercase character data converted to lowercase.

character_expression: The character expression.

LTRIM(character_expression)

Returns the character expression with leading blanks removed.

character_expression: The character expression.

NCHAR(integer_expression)

Returns the Unicode character with the specified integer code as defined by the Unicode standard.

integer_expression: The integer from 0 through 255.

PATINDEX(pattern, expression)

Returns the starting position of the first occurrence of the pattern in the expression. Returns 0 if the pattern is not found.

pattern: The character expression that contains the sequence to be found. The wild-card character % can be used only at the start or end of the expression.
expression: The expression, typically a column, to search for the pattern.

QUOTENAME(character_string [, quote_character])

Returns a valid SQL Server-delimited identifier by adding the necessary delimiters to the specified Unicode string.

character_string: The string of Unicode character data. The string is limited to 128 characters. Inputs greater than 128 characters return null.
quote_character: The optional single character to be used as the delimiter. Can be a single quotation mark, a left or right bracket, or a double quotation mark. If quote_character is not specified brackets are used.

REPLACE(string_expression, string_pattern, string_replacement)

Replaces all occurrences of a string with another string.

string_expression: The string expression to be searched. Can be a character or binary data type.
string_pattern: The substring to be found. Cannot be an empty string.
string_replacement: The replacement string.

REPLICATE ( string_expression ,integer_expression )

Repeats the string value the specified number of times.

string_expression: The string to replicate.
integer_expression: The repeat count.

REVERSE ( string_expression )

Returns the reverse order of the string expression.

string_expression: The string.

RIGHT ( character_expression , integer_expression )

Returns the right part of the string with the specified number of characters.

character_expression: The character expression.
integer_expression: The positive integer that specifies how many characters of the character expression will be returned.

RTRIM(character_expression)

Returns the character expression after it removes trailing blanks.

character_expression: The character expression.

SOUNDEX(character_expression)

Returns the four-character Soundex code, based on how the string sounds when spoken.

character_expression: The alphanumeric expression of character data.

SPACE(repeatcount)

Returns the string that consists of repeated spaces.

repeatcount: The number of spaces.

STARTSWITH(character_expression, character_prefix)

Returns 1 if character_expression starts with character_prefix; otherwise, 0.

character_expression: The character expression.
character_prefix: The character prefix to search for.

STR ( float_expression [ , integer_length [ , integer_decimal ] ] )

Returns the character data converted from the numeric data. For example, STR(123.45, 6, 1) returns 123.5.

float_expression: The float expression.
length: The optional total length to return. This includes decimal point, sign, digits, and spaces. The default is 10.
decimal: The optional number of places to the right of the decimal point. The decimal must be less than or equal to 16.

STUFF(character_expression , integer_start , integer_length , replaceWith_expression)

Inserts a string into another string. It deletes the specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

character_expression: The string expression.
start: The integer value that specifies the location to start deletion and insertion. If start or length is negative, null is returned. If start is longer than the string to be modified, character_expression, null is returned.
length: The integer that specifies the number of characters to delete. If length is longer than character_expression, deletion occurs up to the last character in replaceWith_expression.
replaceWith_expression: The expression of character data that will replace length characters of character_expression beginning at the start value.

SUBSTRING(expression,integer_start,integer_length)

Returns the part of the string with the specified length; starts at the specified index.

expression: The character string.
start: The positive integer that specifies the start index of characters to return.
length: The positive integer that specifies how many characters will be returned.

TOSTRING(string_value1)

Converts the value of this instance to its equivalent string representation.

string_value1: The string to be converted.

TRIM(character_expression)

Returns the character expression with leading and trailing blanks removed.

character_expression: The character expression.

UNICODE(ncharacter_expression)

Returns the integer value defined by the Unicode standard of the first character of the input expression.

ncharacter_expression: The Unicode character expression.

UPPER ( character_expression )

Returns the character expression with lowercase character data converted to uppercase.

character_expression: The character expression.

XML_EXTRACT(xml, xpath [, separator])

Extracts an XML document using the specified XPath to flatten the XML. A comma is used to separate the outputs by default, but this can be changed by specifying the third parameter.

xml: The XML document to extract.
xpath: The XPath used to select the nodes. The nodes selected will be returned in a token-separated list.
separator: The optional token used to separate the items in the flattened response. If this is not specified, the separator will be a comma.

Date Functions

CURRENT_DATE()

Returns the current date value.

CURRENT_TIMESTAMP()

Returns the current time stamp of the database system as a datetime value. This value is equal to GETDATE and SYSDATETIME.

DATEADD (datepart , integer_number , date [, dateformat])

Returns the datetime value that results from adding the specified number (a signed integer) to the specified date part of the date.

datepart: The part of the date to add the specified number to. The valid values and abbreviations are year (yy, yyyy), quarter (qq, q), month (mm, m), dayofyear (dy, y), day (dd, d), week (wk, ww), weekday (dw), hour (hh), minute (mi, n), second (ss, s), and millisecond (ms).
number: The number to be added.
date: The expression of the datetime data type.
dateformat: The optional output date format.

DATEDIFF ( datepart , startdate , enddate )

Returns the difference (a signed integer) of the specified time interval between the specified start date and end date.

datepart: The part of the date that is the time interval of the difference between the start date and end date. The valid values and abbreviations are day (dd, d), hour (hh), minute (mi, n), second (ss, s), and millisecond (ms).
startdate: The datetime expression of the start date.
enddate: The datetime expression of the end date.

DATEFROMPARTS(integer_year, integer_month, integer_day)

Returns the datetime value for the specified year, month, and day.

year: The integer expression specifying the year.
month: The integer expression specifying the month.
day: The integer expression specifying the day.

DATENAME(datepart , date)

Returns the character string that represents the specified date part of the specified date.

datepart: The part of the date to return. The valid values and abbreviations are year (yy, yyyy), quarter (qq, q), month (mm, m), dayofyear (dy, y), day (dd, d), week (wk, ww), weekday (dw), hour (hh), minute (mi, n), second (ss, s), millisecond (ms), microsecond (mcs), nanosecond (ns), and TZoffset (tz).
date: The datetime expression.

DATEPART(datepart, date [,integer_datefirst])

Returns a character string that represents the specified date part of the specified date.

datepart: The part of the date to return. The valid values and abbreviations are year (yy, yyyy), quarter (qq, q), month (mm, m), dayofyear (dy, y), day (dd, d), week (wk, ww), weekday (dw), hour (hh), minute (mi, n), second (ss, s), millisecond (ms), microsecond (mcs), nanosecond (ns), TZoffset (tz), and ISO_WEEK (isowk,isoww).
date: The datetime string.
datefirst: The optional integer representing the first day of the week. The default is 7, Sunday.

DATETIME2FROMPARTS(integer_year, integer_month, integer_day, integer_hour, integer_minute, integer_seconds, integer_fractions, integer_precision)

Returns the datetime value for the specified date parts.

year: The integer expression specifying the year.
month: The integer expression specifying the month.
day: The integer expression specifying the day.
hour: The integer expression specifying the hour.
minute: The integer expression specifying the minute.
seconds: The integer expression specifying the seconds.
fractions: The integer expression specifying the fractions of the second.
precision: The integer expression specifying the precision of the fraction.

DATETIMEFROMPARTS(integer_year, integer_month, integer_day, integer_hour, integer_minute, integer_seconds, integer_milliseconds)

Returns the datetime value for the specified date parts.

year: The integer expression specifying the year.
month: The integer expression specifying the month.
day: The integer expression specifying the day.
hour: The integer expression specifying the hour.
minute: The integer expression specifying the minute.
seconds: The integer expression specifying the seconds.
milliseconds: The integer expression specifying the milliseconds.

DAY(date)

Returns the integer that specifies the day component of the specified date.

date: The datetime string that specifies the date.

EOMONTH(start_date [, integer_month_to_add ])

Returns the last day of the month that contains the specified date with an optional offset.

start_date: The datetime expression specifying the date for which to return the last day of the month.
month_to_add: The optional integer expression specifying the number of months to add to start_date.

GETDATE()

Returns the current time stamp of the database system as a datetime value. This value is equal to CURRENT_TIMESTAMP and SYSDATETIME.

GETUTCDATE()

Returns the current time stamp of the database system formatted as a UTC datetime value. This value is equal to SYSUTCDATETIME.

ISDATE(date, [date_format])

Returns 1 if the value is a valid date, time, or datetime value; otherwise, 0.

date: The datetime string.
date_format: The optional datetime format.

SMALLDATETIMEFROMPARTS(integer_year, integer_month, integer_day, integer_hour, integer_minute)

Returns the datetime value for the specified date and time.

year: The integer expression specifying the year.
month: The integer expression specifying the month.
day: The integer expression specifying the day.
hour: The integer expression specifying the hour.
minute: The integer expression specifying the minute.

SYSDATETIME()

Returns the current time stamp as a datetime value of the database system. It is equal to GETDATE and CURRENT_TIMESTAMP.

SYSUTCDATETIME()

Returns the current system date and time as a UTC datetime value. It is equal to GETUTCDATE.

TIMEFROMPARTS(integer_hour, integer_minute, integer_seconds, integer_fractions, integer_precision)

Returns the time value for the specified time and with the specified precision.

hour: The integer expression specifying the hour.
minute: The integer expression specifying the minute.
seconds: The integer expression specifying the seconds.
fractions: The integer expression specifying the fractions of the second.
precision : The integer expression specifying the precision of the fraction.

YEAR(date)

Returns the integer that specifies the year of the specified date.

date: The datetime string.

Math Functions

ABS ( numeric_expression )

Returns the absolute (positive) value of the specified numeric expression.

numeric_expression: The expression of an indeterminate numeric data type except for the bit data type.

ACOS ( float_expression )

Returns the arc cosine, the angle in radians whose cosine is the specified float expression.

float_expression: The float expression that specifies the cosine of the angle to be returned. Values outside the range from -1 to 1 return null.

ASIN ( float_expression )

Returns the arc sine, the angle in radians whose sine is the specified float expression.

float_expression: The float expression that specifies the sine of the angle to be returned. Values outside the range from -1 to 1 return null.

ATAN ( float_expression )

Returns the arc tangent, the angle in radians whose tangent is the specified float expression.

float_expression: The float expression that specifies the tangent of the angle to be returned.

ATN2 ( float_expression1 , float_expression2 )

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.

float_expression1: The float expression that is the y-coordinate.
float_expression2: The float expression that is the x-coordinate.

CEILING ( numeric_expression )

Returns the smallest integer greater than or equal to the specified numeric expression.

numeric_expression: The expression of an indeterminate numeric data type except for the bit data type.

COS ( float_expression )

Returns the trigonometric cosine of the specified angle in radians in the specified expression.

float_expression: The float expression of the specified angle in radians.

COT ( float_expression )

Returns the trigonometric cotangent of the angle in radians specified by float_expression.

float_expression: The float expression of the angle in radians.

DEGREES ( numeric_expression )

Returns the angle in degrees for the angle specified in radians.

numeric_expression: The angle in radians, an expression of an indeterminate numeric data type except for the bit data type.

EXP ( float_expression )

Returns the exponential value of the specified float expression. For example, EXP(LOG(20)) is 20.

float_expression: The float expression.

EXPR ( expression )

Evaluates the expression.

expression: The expression. Operators allowed are +, -, *, /, ==, !=, >, <, >=, and <=.

FLOOR ( numeric_expression )

Returns the largest integer less than or equal to the numeric expression.

numeric_expression: The expression of an indeterminate numeric data type except for the bit data type.

LOG ( float_expression [, base ] )

Returns the natural logarithm of the specified float expression.

float_expression: The float expression.
base: The optional integer argument that sets the base for the logarithm.

LOG10 ( float_expression )

Returns the base-10 logarithm of the specified float expression.

float_expression: The expression of type float.

PI ( )

Returns the constant value of pi.

POWER ( float_expression , y )

Returns the value of the specified expression raised to the specified power.

float_expression: The float expression.
y: The power to raise float_expression to.

RADIANS ( float_expression )

Returns the angle in radians of the angle in degrees.

float_expression: The degrees of the angle as a float expression.

RAND ( [ integer_seed ] )

Returns a pseudorandom float value from 0 through 1, exclusive.

seed: The optional integer expression that specifies the seed value. If seed is not specified, a seed value at random will be assigned.

ROUND ( numeric_expression , integer_length [ ,function ] )

Returns the numeric value rounded to the specified length or precision.

numeric_expression: The expression of a numeric data type.
length: The precision to round the numeric expression to.
function: The optional type of operation to perform. When the function parameter is omitted or has a value of 0 (default), numeric_expression is rounded. When a value other than 0 is specified, numeric_expression is truncated.

SIGN ( numeric_expression )

Returns the positive sign (1), 0, or negative sign (-1) of the specified expression.

numeric_expression: The expression of an indeterminate data type except for the bit data type.

SIN ( float_expression )

Returns the trigonometric sine of the angle in radians.

float_expression: The float expression specifying the angle in radians.

SQRT ( float_expression )

Returns the square root of the specified float value.

float_expression: The expression of type float.

SQUARE ( float_expression )

Returns the square of the specified float value.

float_expression: The expression of type float.

TAN ( float_expression )

Returns the tangent of the input expression.

float_expression: The expression of type float.