Functions (Pipeline) Reference
The following functions are used with the expression and decision operators in a pipeline.
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:
|
|
FORMAT_NUMBER(value, format) |
Returns a formatted representation of the given value. |
|
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 |
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'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 |
The first argument is a Date object representing October 13, 2020.
|
WEEKOFYEAR(date) |
Returns the date's week in the year. |
|
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 code examples:
|
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 | 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 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. |
|
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_STRUCT(string,column[,string,column]*) | Creates a new column of Struct type. The input columns must be grouped as key-value pairs. |
|
TO_ARRAY(column[,column]*) | Creates a new column as Array type. The input columns must all have the same data type. |
|
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" |
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('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 If |
|
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 |
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 |