Oracle Cloud Infrastructure Documentation

Skip to main content

Functions (Pipeline) Reference

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

Arithmetic Functions


Function	Description	Example
`ABS(numeric)`	Returns the absolute power of the `numeric` value.	`ABS(-1)`
`CEIL(numeric)`	Returns the smallest integer not greater than the `numeric` value	`CEIL(-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


Function	Description	Example
`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


Function	Description	Example
`CURRENT_DATE`	Returns the current date.	`CURRENT_DATE` returns today's date
`CURRENT_TIMESTAMP`	Returns 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 is 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 is 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


Function	Description	Example
`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


Function	Description	Example
`AND`	The 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"
`OR`	The 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"
`NOT`	The 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


Functions	Description	Example
`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 columns	`CONCAT('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`