Functions (Pipeline) Reference

The following functions are used with the expression and decision operators in a pipeline.

Arithmetic Functions
FunctionDescriptionExample
ABS(numeric)Returns the absolute power of the numeric value.ABS(-1)
CEIL(numeric)Returns the smallest integer not greater than the numeric valueCEIL(-1,2)
EXP(numeric) Returns e raised to the power of numeric. The constant e equals 2.71828182845904, the base of the natural logarithm. EXP(2)
FLOOR(numeric)Returns the largest integer not greater than the numeric value.FLOOR(-1,2)
MOD(numeric1, numeric2)Returns the remainder after numeric1 is divided by numeric2.MOD(8,2)
POW(numeric1, numeric2)Raises numeric1 to the power of numeric2.POW(2,3)
ROUND(numeric1, numeric2)Returns numeric1 rounded to numeric2 decimal places.ROUND(2.5,0)
TRUNC(numeric1, numeric2)Returns numeric1 truncated to numeric2 decimal places.TRUNC(2.5,0)
TO_NUMBER(expr[, format, locale])Converts an expr to a number, based on the format and optional locale provided. Default locale is en-US. Supported language tags.

Supported format patterns:

  • 0: A digit
  • #: A digit, zero shows as absent
  • .: Placeholder for decimal separator
  • ,: Placeholder for grouping separator
  • E: Separates mantissa and exponent for exponential formats
  • -: Default negative prefix
  • ¤: Currency sign; replaced by currency symbol; if doubled, replaced by international currency symbol; if present in a pattern, the monetary decimal separator is used instead of the decimal separator

TO_NUMBER('5467.12') returns returns 5467.12

TO_NUMBER('-USD45,677.7', '¤¤##,###.#', 'en-US') returns -45677.7

FORMAT_NUMBER(value, format) Returns a formatted representation of the given value.

FORMAT_NUMBER(123.4567, '^-09.3f') returns 0123.4570

Conditional Functions
FunctionDescriptionExample
NULLIF(value, value)Returns null if the two values equal each other, otherwise returns the first value.NULLIF('ABC','XYZ') returns ABC
ISNULL(value) Checks for null value. ISNULL('ABC') returns false
ISNOTNULL(value) Checks for not null value. ISNOTNULL('ABC') returns true
Date and Time Functions
FunctionDescriptionExample
CURRENT_DATEReturns the current date.CURRENT_DATE returns today's date
CURRENT_TIMESTAMPReturns the current date and time with time zone in the session time zone.CURRENT_TIMESTAMP returns today's date and current time
DATE_ADD(date, number)Returns the date that's the specified number of days after the specified date.DATE_ADD('2017-07-30', 1) returns 2017-07-31
DATE_SUB(date, number) Returns the date that's the specified number of days before the specified date. DATE_SUB('2017-07-30', 1) returns 2017-07-29
DATE_FORMAT(expr, format[, locale])Formats an expr of Date, based on the format and optional locale provided. Default locale is en-US. Supported language tags.

In pipeline expressions, the format must use the strftime format codes.

DATE_FORMAT(2020-10-13, '%d-%m-%Y') returns '13-10-2020'.

The first argument is a Date object representing October 13, 2020.

DATE_FORMAT(2018-junio-17, '%d/%B/%y', 'es-ES') returns '17/junio/18'

WEEKOFYEAR(date) Returns the date's week in the year.

WEEKOFYEAR('2022-07-28') returns 30

WEEKOFYEAR('2022-07-28 13:24:30') returns 30

TO_DATE(string, format_string[, localeStr])Parses the string expression with the format_string expression to a date. Locale is optional. Default is en-US. Supported language tags.

In pipeline expressions, the format_string must use the strftime format codes.

Format code examples:

'%a %d-%m-%Y' returns Tue 10-12-2019

'%a %d/%m/%Y' returns Tue 10/12/2019

'%a %d/%m/%y' returns Tue 10/12/19

TO_DATE('12 June 2018', '%m/%d/%Y') returns 06/12/2018

TO_TIMESTAMP(expr, format_string[, localeStr])Converts an expr of VARCHAR to a value of TIMESTAMP, based on the format_string and the optional localeStr provided.

In pipeline expressions, the format_string must use the strftime format codes.

TO_TIMESTAMP('2020-10-10 11:10:10', '%A %d-%m-%Y, %H:%M:%S') returns a TIMESTAMP object representing Saturday 10-10-2020, 11:10:10
MONTHS_BETWEEN(start_date_expr, end_date_expr)

Returns the number of months between start_date_expr and end_date_expr. start_date_expr and end_date_expr can be a date, timestamp or string with a format such as yyyy-MM-dd or yyyy-MM-dd HH:mm:ss.SSS

A whole number is returned if both dates are the same day of the month, or both are the last day in their respective months. Otherwise, the difference is calculated based on 31 days per month.

MONTHS_BETWEEN('2022-01-01', '2022-01-31') returns 1

MONTHS_BETWEEN('2022-07-28', '2020-07-25') returns 24

MONTHS_BETWEEN('2022-07-28 13:24:30', '2020-07-25 13:24:30') returns 24

Hierarchical Functions
FunctionDescriptionExample
TO_MAP(string,column[,string,column]*)Creates a new column of Map type. The input columns must be grouped as key-value pairs. The input key columns cannot be null, and must all have the same data type. The input value columns must all have the same data type.

TO_MAP('Ename',Expression_1.attribute1) returns a Map type column: {"ENAME" -> 100}

TO_MAP('block', EXPRESSION_1.MYSOURCE.address.block, 'unit', EXPRESSION_1.MYSOURCE.address.unit) returns a Map type column: {"block" -> 1,"unit" -> 1}

TO_STRUCT(string,column[,string,column]*)Creates a new column of Struct type. The input columns must be grouped as key-value pairs.

TO_STRUCT('Ename',Expression_1.attribute1) returns {100}

TO_STRUCT('Id',Expression_1.attribute1, 'Name', Expression_1.attribute2) returns {100, "John"}

TO_ARRAY(column[,column]*)Creates a new column as Array type. The input columns must all have the same data type.

TO_Array(Expression_1.attribute1) returns [100]

TO_ARRAY(EXPRESSION_1.attribute2,EXPRESSION_1.attribute3) returns ["John","Friend"]

Operator (Comparison) Functions
FunctionDescriptionExample
ANDThe logical AND operator. Returns true if both operands are true, otherwise returns false.(x = 10 AND y = 20) returns "true" if x is equal to 10 and y is equal to 20. If either one is not true, then it returns "false"
ORThe logical OR operator. Returns true if either operand is true or both are true, otherwise returns false.(x = 10 OR y = 20) returns "false" if x is not equal to 10 and also y is not equal to 20. If either one is true, then it returns "true"
NOTThe logical NOT operator.
IN Tests whether an expression matches a list of values. FILTER_2.ORDERS.ORDER_ID IN (1003, 1007)
=Tests for equality. Returns true if expr1 equals expr2, otherwise returns false.x = 10 returns "true" when value of x is 10, else it returns "false"
>Tests for an expression greater than. Returns true if expr1 is greater than expr2.x > 10 returns "true" if value of x is greater than 10, else it returns "false"
>=Tests for an expression greater than or equal to. Returns true if expr1 is greater than or equal to expr2.x > =10 returns "true" if value of x is greater than or equal to 10, else it returns "false"
<Tests for an expression less than. Returns true if expr1 is less than expr2.x < 10 returns "true" if value of x is less than 10, else it returns "false"
<=Tests for an expression less than or equal to. Returns true if expr1 is less than or equal to expr2.x <= 10 returns "true" if value of x is less than 10, else it returns "false"
String Functions
FunctionsDescriptionExample
CAST(value AS type)Returns the specified value in the specified type.CAST("10" AS INT) returns 10
CONCAT(string, string)Returns the combined values of strings or columnsCONCAT('Oracle','SQL') returns OracleSQL
LOWER(string)Returns the string with all letters changed to lowercase.LOWER('ORACLE') returns oracle
LENGTH(string)Returns the character length of string or the number of bytes of binary data. The length of the string includes trailing spaces.LENGTH('Oracle') returns 6
LTRIM(string)Returns the string with leading spaces removed from the left.LTRIM(' Oracle')
REGEXP_EXTRACT(string, regexp[, RegexGroupIdx])Extracts a group that matches the regular expression.REGEXP_EXTRACT('22;33;44', '([0-9.]*);([0-9.]*);([0-9.]*)', 1) returns 22
REGEXP_SUBSTR(string, regexp[, RegexGroupIdx]) Searches and extracts the string that matches a regular expression pattern from the input string. If the optional capturing group index is provided, the function extracts the specific group.

REGEXP_SUBSTR('https://www.oracle.com/products', 'https://([[:alnum:]]+\.?){3,4}/?') returns https://www.oracle.com

REGEXP_SUBSTR('22;33;44', '([0-9.]*);([0-9.]*);([0-9.]*)', 1) returns 22
REGEXP_REPLACE(string, regexp, rep]) Replaces all substrings of string that match the regular expression with rep
REPLACE(string, search, replacement)Replaces all occurrences of search with replacement.

If search is not found in string, then string is returned unchanged.

If replacement is not specified or is an empty string, nothing replaces the search that is removed from string.

REPLACE('input string value', 'input', 'output') returns "output string value". For example:

REPLACE('ABCabc', 'abc', 'DEF') returns ABCDEF

RTRIM(string)Returns the string with leading spaces removed from the right.RTRIM('Oracle ')
SUBSTRING(string, start_position[, substring_length])Returns the substring from string starting at the given start_position with the substring_length length.

Offset position numbering begins with 0.

SUBSTRING('Hello Oracle', 6, 6) returns Oracle
TO_CHAR(expr[, format_string])Converts a date into a string, based on the format. Converts a number into a string, no format is required.

In pipeline expressions, the format_string must use the strftime format codes.

TO_CHAR(123) returns 123

TO_CHAR(Date'2020-10-30', '%m/%d/%Y') returns 10/30/2020.

The first argument is a Date object representing October 30th, 2020.

TRIM(string) Returns string with the leading space characters removed. TRIM(' ORACLE ') returns ORACLE
UPPER(string)Returns a string with all letters changed to uppercase.UPPER('oracle') returns ORACLE
json_path(json_string, json_filter_path) Extracts a value from a JSON structure. CAST(json_path(SYS.RESPONSE_PAYLOAD, '$.key') AS String) returns string value of field "key" in RESPONSE_PAYLOAD