ONE Expressions

Subtraction of numeric operands a and b.

Negation of numeric operand a, for example -(a*c).

Division of numeric operands a and b.

Multiplication of numeric operands a and b.

Modulo: the remainder after numerical division of a by b.

Addition of numeric operands a and b, or string concatenation.

Division of integer operands without a remainder.

Logical conjunction.

Logical negation.

Logical sum.

Exclusive OR.

Tests if the value a is less than b.

Tests if the value a is less than or equal to b.

Tests the negated equivalence of two values.

Tests the equivalence of two values.

Tests if the value a is greater than b.

Tests if the value a is greater than or equal to b.

Tests whether a is member of the specified set. As opposed to the "is in" operation, if a is not member of the set and a null value is a member of the set, then the result is NULL.

Tests whether a is a member of the specified set. Always returns TRUE or FALSE.

Tests whether a is not a member of the specified set.

Tests whether a is not a member of the specified set. As opposed to the "is not in" operation, if a is not member of the set and a null value is a member of the set, then the result is NULL.

The function returns the value of the property or the default value if there is no property with that key. All property names must have a prefix defining where the property should be read from.

The following prefixes are available:

The function returns the product version.

Tests if a is equal to b. Null values are allowed as operands.

A typical use is a is null.

Tests if a is not equal to b. Null values are allowed as operands.

A typical use is a is not null.

The function sets the value of the property. All property names must have a prefix defining the category where the property should be stored to. Not all property categories allow the definition of new properties (for example, Java system properties - prefix system. cannot be set by ONE Desktop).

A typical use is setParameterValue("http.outputParam", "paramValue").

Calculates the shortest distance (in meters) between two points on Earth. A point is defined by its latitude and longitude in degrees.

Generates the next number each time it is called.

Behind the scenes, the following happens: when called, the operation checks whether it was already called with the given name in the running plan or in the online server. If it was not called previously, it creates a counter object that stores the last generated number; in the first iteration, the number is the value of the start parameter.

Otherwise, it takes the already created counter object, increments the value of the object by the step parameter, and returns the stored number.

If there are more named sequences with different start and step parameters called in the plan, then the counter object is created with the parameters of the first call. The default values are namedSequence(0,1).

Generates a random number from the interval defined by the parameters from and to.

The from parameter must be a number lower or equal to the to parameter, otherwise the null value is returned. If any of the parameters evaluates to null value, null is returned as well.

The default values are 0 for the from parameter and 1 for the to parameter.

A static factory to retrieve a type 4 (pseudo randomly generated) UUID. The UUID is generated using a cryptographically strong pseudorandom number generator.

It generates the next number each time it is called.

Behind the scenes, the following happens: when called, the operation checks whether it was already called with the given parameters in the running plan or in the online server. If it was not called previously, it creates a counter object that stores the last generated number; in the first iteration, the number is the value of the start parameter.

Otherwise, it takes the already created counter object, increments the value of the object by the step parameter, and returns the stored number.

The default values are sequence(0,1).

Adds the specified srcValue of the type specified by fieldName (YEAR, MONTH, DAY, HOUR, MINUTE, or SECOND) to the srcDate.

This function allows subtraction, so the srcValue can be negative. The return value is the result of the add(subtract) operation.

If any of the operands are invalid or if an unsupported fieldName is added to the data type, then the expression reports an error.

Returns the difference between endDate and startDate expressed in fieldName units.

If the result exceeds the maximum range of INTEGER, then the value NULL is returned. If any of the parameters are invalid, the expression reports an error.

A combination of date type DAY and fieldName HOUR, MINUTE, SECOND can be used (the value of these fields is considered to be 0).

This function returns the value of the field fieldName of srcDate.

If any of the parameters are invalid, the expression reports an error. For the fields HOUR, MINUTE, and SECOND set for the date-type DAY, the function returns 0.

The function truncates less important parts of the srcDate up to the level specified by fieldName. Truncation changes values of the fields by the following rules: MONTH and DAY to 1, HOUR, MINUTE, and SECOND to 0.

The function can be used even for the DAY type with the fieldName HOUR, MINUTE, and SECOND. The function does not have an effect on the data - the result and input values are the same.

If any of the parameters are invalid, the expression reports an error.

Example: for srcDate "5.5.1980 12:35:10" and fieldName "HOUR", the function returns 5.5.1980 12:00:00.

Returns the date in the format defined by the specified srcExpression (type DAY or DATETIME), with the time set to zero (HH:mm:ss.SSS).

Returns the time when the current request processing was started. This is the ONE application start time in batch mode and the web service request time in online mode.

Returns the current time with the type DATETIME. This function always returns the time when it is evaluated, that is, the current time.

Returns the current date in type DAY. This function is designed to return the same value for all records (ONE application start date), even if ONE runs past midnight.

Transforms all words in the string srcStr in the following manner: the first character of each word to uppercase and all following characters to lowercase.

A word consists of alphabetic characters (letters). All other characters are considered separators.

Transforms all words in the string srcStr (with the exception of the words given as parameters exc) in the following manner: the first character of each word to uppercase and all following characters to lowercase.

A word consists of alphabetic characters (letters). All other characters are considered separators.

A current downside is that words from exc are transformed to lowercase. If the parameter leaveExcsAsTheyAre is set to true, the transformation leaves words from exc the way they are in the string srcStr, that is, without any change.

Searches for the occurrence of the word srcWord in the string srcStr.

A word is a sequence of letters with no whitespaces. Words in the string are defined as sequences of letters separated by a space (' ' or the given separator). The leading, trailing, and multiple spaces are ignored.

Returns the number of characters from the string srcStr that include diacritical marks.

Takes a string as an input wrongly read using the actualCp charset and transforms it into a correct correctCp charset.

For example, if you have a file that is entirely in windows-1250 charset except for one column, a, which is in the latin2 charset, it will be read using the windows-1250 charset and for the a column the expression cpConvert(a, 'windows-1250', 'latin2') can be used.

Returns the diceCoefficient for strings srcStr1 and srcStr2. Returned values are between 0 and 1, where 1 indicates a perfect match (identical strings) and 0 no similarity. The function is case insensitive.

The method uses a set similarity measure called Sørensen-Dice coefficient. It uses the following formula: DQ(A, B) = (2 * |A ∩ B|) / (|A| + |B|), where A, B are sets.

Since the measure is originally used for comparing two sets, this implementation offers three ways of slicing the input string to "tokens" (set elements). They are distinguished by the method signature:

Returns a string which contains the concatenated first occurrence of parts of the original string srcStr. If the string srcStr is null or empty, the function returns NULL.

Encodes srcStr to a double metaphone primary or secondary string based on the parameter isAlternate. It removes accents from srcStr before evaluating the double metaphone value.

If isAlternate is true, it returns the secondary string, otherwise it returns the primary string.

Returns the edit distance between strings srcStr1 and srcStr2.

The parameter maxDistance defines the maximum value to which the distance is calculated (null or a negative value is considered as 0). Strings with distance greater than this value are considered to have distance maxDistance + 1.

The parameter caseInsensitive determines whether case sensitivity should be considered or not. A null value is considered as false. By default, the function is case insensitive.

The difference between Levenshtein and Edit distance lies in the definition of distance of two switched adjacent characters. The Levenshtein one considers the switch as two changes whereas the Edit distance considers the switch to be a single change.

If both of the strings are null, then the result is 0. If only one of the strings is null, then the result is the length of the other string.

This function is also known as Damerau-Levenshtein distance.

Removes spaces between separate characters (words of length 1``) in the string srcStr.

The parameter minLength specifies the minimum length of the newly created word (that is, spaces are removed only if, after their removal, the result has the length of at least minLength).

The parameter onlyUpper is a Boolean value which restricts the space removal. If set to TRUE, then only spaces between capital letters are processed. If set to FALSE, then all spaces between separate characters are processed.

Verifies whether the string srcStr or its parts match the regular expression srcRegex.

The parameter caseInsensitive determines whether the case sensitivity should be considered or not. By default, the function is case sensitive.

If the string srcStr is null or empty, the function returns NULL. For information about regular expressions, see Regular expressions.

Returns the Hamming distance between strings srcStr1 and srcStr2.

The parameter caseInsensitive determines whether case sensitivity should be considered or not. A null value is considered as false. By default, the function is case insensitive.

If both of the strings are null, then the result is 0. If only one of the strings is null, then the result is the length of the other string.

Returns the index within the string srcStr of the first occurrence of the specified substring subStr, starting at the index fromIndex.

If the substring is not found, the value NULL is returned. If the value fromIndex exceeds the length of the string srcStr, the value NULL is returned.

If the value fromIndex < 0, the start of the search is counted relative to the end of the string. However, if the counted start overlaps the string start, then the search starts at the beginning of the string srcStr instead.

The index of the first character is 0.

Searches for the string srcStr in a file defined by the parameter fileName. The parameter fileName must be a constant expression and must point to a dictionary with simple values.

The function returns TRUE if srcStr is found in the dictionary, and FALSE otherwise. Before the search starts, the value of srcStr is trimmed (all whitespaces from the beginning and end of the string are removed), which can potentially lead to null value of the search value.

Verifies whether the string srcStr represents a number. All characters of the string must be digits except for the first character which can be either a plus (+) or a minus sign (-). Decimal numbers are evaluated as not being numbers (that is, the characters such as a dot (.) or comma (,) are not allowed).

Returns the jaccardCoefficient for strings srcStr1 and srcStr2. The returned values are between 0 and 1, where 1 indicates a perfect match (identical strings) and 0 no similarity. The function is case insensitive.

The method uses a set similarity measure called Jaccard coefficient. It uses the following formula: JQ(A, B) = (|A ∩ B|) / (|A ∪ B|), where A, B are sets.

Since the measure is originally used for comparing two sets, this implementation offers three ways of slicing the input string to "tokens" (set elements). They are distinguished by the method signature:

Returns the Jaro-Winkler score for strings srcStr1 and srcStr2. The returned values are between 0 and 1, where 1 indicates a perfect match (identical strings) and 0 no similarity. It is suited for evaluating short strings (for example, personal names).

This is a combination of Jaro’s algorithm with Winkler’s boost for identical prefixes. For an extensive description of the calculation, see Jaro-Winkler distance.

To turn off the Winkler’s prefix boost (obtain pure Jaro), set the boost threshold to a value greater than 1.0. The default values for boostTreshold and prefixLength are considered to be optimal (according to original Winkler’s papers), but you can adjust these to finetune your results.

By default, the function is case insensitive. When case sensitivity is turned on, uppercase and lowercase variants of the same letter are considered as two completely different characters.

There is currently no support or optimization for diacritical marks (the function considers c and č as two completely different characters).

Returns the index within the string srcStr of the last (rightmost) occurrence of the substring subStr, starting at the index fromIndex.

If the substring is not found, the value NULL is returned. If the value fromIndex exceeds the length of the string srcStr, the value NULL is returned.

If the value fromIndex < 0, the start of search is counted relative to the end of the string. However, if the counted start overlaps the string start, then the search starts at the beginning of the string srcStr.

The index of the first character is 0.

Returns a new string that is a substring of srcStr containing the number of characters taken from the start of srcStr.

Returns the number of characters in the string srcStr.

Returns the Levenshtein distance between strings srcStr1 and srcStr2.

The parameter maxDistance defines the maximum value to which the distance is calculated (null or a negative value is considered as 0). Strings with distance greater than this value are considered to have distance maxDistance + 1.

The parameter caseInsensitive determines whether the case sensitivity should be considered or not. A null value is considered as false. By default, the function is case insensitive.

If both of the strings are null, then the result is 0. If only one of the strings is null, then the result is the length of the other string.

Transforms all characters of the string srcStr to lowercase.

Verifies whether the string srcStr matches exactly the pattern of the regular expression srcRegex.

The parameter caseInsensitive determines whether case sensitivity should be considered or not. By default, the function is case sensitive.

If the string srcStr is null or empty, the function returns NULL.

For information about regular expressions, Regular expressions.

Encodes the string srcStr to a metaphone string. It removes accents from srcStr before evaluating the metaphone value.

Takes`srcStr` as a sequence of items separated by inSeparator and creates another sequence of all n-grams of these items (subsequences of the original sequence) in the original order.

N-grams in the output sequence are separated by inSeparator, items inside n-grams by outSeparator. The size parameter is the "N", the size of the n-grams.

The length of both separators as well as of the n-grams has to be at least 1.

If there are not enough words for creating an n-gram, the function returns NULL.

Transforms all characters in srcStr to match the case of characters in maskStr. If maskStr is shorter than srcStr, the exceeding characters of srcStr are not modified.

Returns a copy of the string srcStr in which all characters containing diacritics are replaced by the corresponding characters without them. Only Latin characters with an accent defined in the Unicode Standard version 5.0.0 are changed.

Replaces occurrences of the string what with the string withWhat in the string srcStr.

The function returns NULL when srcStr or what is null or an empty string. Overlapping occurrences of the string what are replaced only once, that is, replace("conoconoco", "conoco", "XXXX") returns "XXXXnoco".

Returns n copies of the string srcStr concatenated together without any separator. If n ⇐ 0 or srcStr = "", then the result value is NULL.

Returns a new string that is a substring of srcStr containing the number of characters taken from the end of srcStr.

Returns a string that consists of sorted parts of the string srcStr. If the parameter srcLocale is set, then the sort is done for the given locale.

The parameter srcSeparator specifies the separator of the string parts. If missing, set to null, or empty, the input string srcStr is parsed to separate characters that are then sorted.

If the Boolean parameter srcDesc is set to TRUE, reverse sort order is used.

If the string srcStr is null or empty, the function returns NULL.

Returns the soundex value of the srcStr parameter. It removes accents and non-ASCII characters from srcStr before evaluating the soundex value.

Removes whitespace characters from both ends of the string srcStr and reduces multiple whitespace characters within the string. The only whitespace character is the ' ' character.

Replaces all occurrences of srcPattern in the string srcStr with srcReplacement.

If the parameter caseInsensitiveFlag is set to TRUE, then the search for srcPattern is case insensitive. For information about regular expressions, see Regular expressions.

Replaces all occurrences of srcPattern in the string srcStr with srcReplacement, with the maximum number of replacements defined by the parameter srcVolume. If the total number of replacements in the string srcStr exceeds the srcVolume parameter, only the first srcVolume replacements are applied.

If the parameter caseInsensitiveFlag is set to TRUE, then the search for srcPattern is case insensitive. For information about regular expressions, see Regular expressions.

Returns a new string that is a substring of the string srcStr. The substring begins at the index beginIndex and extends to the character at index beginIndex + strLen - 1.

If beginIndex < 0, then beginIndex is set to beginIndex + length(srcStr). If beginIndex is still < 0, beginIndex is set to 0.

If strLen is not specified or is greater than length(srcStr) - beginIndex, the rest of srcStr is returned. If strLen < 0, strLen is set to 0.

An empty substring is returned as a NULL string.

The index of the first character is 0.

Transforms characters of the string srcStr.

The transformation replaces all occurrences of any character named in the parameter charsFrom with the corresponding character defined in the parameter charsTo at their corresponding positions, that is, transliterate("name@company.com","@","*") becomes "name*company.com".

Removes all consonants (only Latin letters are concerned) and their accented equivalents from the string srcStr. Other characters (digits, punctuation) remain unchanged.

Deprecated, replaced by the function removeAccents.

Returns a string that consists only of the digits included in the original string srcStr. All other characters are discarded.

Returns a string that consists only of the letters included in the original string srcStr. All other characters are discarded.

Removes all vowels (only Latin letters are concerned) and their accented equivalents from the string srcStr. Other characters (digits, punctuation) remain unchanged.

Removes whitespace or specified characters from both ends of the string srcStr. Whitespace characters are '\t', '\n', '\f', '\r', and ' '.

For more information, see the trim method of the class java.lang.String in the Java API.

Removes whitespace or specified characters from the left side of the string srcStr. Whitespace characters are '\t', '\n', '\f', '\r', and ' '.

For more information, see the trim method of the class java.lang.String in the Java API.

Removes whitespace or specified characters from the right side of the string srcStr. Whitespace characters are '\t', '\n', '\f', '\r', and ' '.

For more information, see the trim method of the class java.lang.String in the Java API.

Transforms all characters of the string srcStr to uppercase.

Returns the srcIdx-th word from the string srcStr.

Words are defined as sequences of letters separated by srcSeparator. If the srcSeparator is missing or null, then the space character is assumed.

The index of the first word is 0.

Returns all size-sized combinations of words in the string srcStr.

Words are defined as sequences of characters separated by srcSeparator. Leading, trailing, and multiple separators are ignored.

The words in the combination are separated by srcSeparator, the combinations are separated by combSeparator. The order of the words in the combinations is the same as the order of the words in the string srcStr.

If any of the arguments is null or size is less than 1, NULL is returned. If size is greater than or equal to the number of words in srcStr, srcStr is returned.

Returns the number of non-empty words in the string srcStr.

Words are defined as sequences of characters separated by srcSeparator. Leading, trailing, and multiple separators are ignored. If the srcSeparator is missing or null, then the space character is assumed.

Decodes the source string from Base64.

Creates a hash-based message authentication code using MD5 algorithm.

Creates a hash-based message authentication code using SHA-1 algorithm.

Creates a hash-based message authentication code using SHA-512 algorithm.

Converts XML/HTML special characters (<, >, &, "…​) into their corresponding entities (<, >, &, ",…​). The optional allEntities parameter defaults to false.

If set to true, it also converts all non-ASCII characters into known entities or into the &#xHHHH; format.

Transforms entities representing XML/HTML special characters (<, >, &, ",…​) back to their original symbols (<>&"…​). The optional allEntities parameter defaults to false. When true, it converts all entities, including those in the &#xHHHH; format, back to characters.

The entity   is converted to a standard space.

Creates a hash of the source string using MD5 algorithm.

Encodes the source string to Base64.

Decodes a URL-encoded string, converting hexadecimal byte representations (%HH) back to the original characters. The charset parameter is optional (default is according to system settings), and is essential for char-byte conversion.

Encodes a string for safe URL use by converting unsupported characters into their hexadecimal (%HH) representations. Blank spaces are converted to the + symbol. Alphanumeric characters along with *-._ are inherently supported. The charset parameter is optional (default is according to system settings), and is essential for char-byte conversion.

Deprecated, use coding.toBase64 instead.

Deprecated, use coding.HMacMD5 instead.

Deprecated, use coding.HMacSHA1 instead.

Deprecated, use coding.HMacSHA512 instead.

Deprecated, use coding.md5 instead.

Arc cosine of x. Defined for x ∈ [-1,1].

Arc sine of x. Defined for x ∈ [-1,1].

Arc tangent of x.

Cosine of x. The argument is assumed in radians.

Returns Euler’s number (e) = 2,7182818285.

Exponential function ex. Equivalent to math.pow(math.e(), x).

Natural logarithm of x. Defined for x > 0 and math.exp(math.log(x)) = x.

Common logarithm of x. Defined for x > 0 and math.pow(10, math.log10(x)) = x.

Returns π (PI) = 3,1415926536.

Returns x power to y. The function is undefined and returns NULL value for negative y and x = 0 or negative x and non-integer y. The function variant for integer x returns NULL when exponent y < 0 and x is not ±1.

Sine of x. The argument is assumed in radians.

Returns square of x.

Square root of x. Defined for x >= 0.

Tangent of x. The argument is assumed in radians. Undefined for π/2 + k*π.

Bitwise AND

Bitwise NOT, or complement

Bitwise inclusive OR

Bitwise exclusive or

any max(any a, any b)

Returns the greater of two operands. If either of the operands is null, NULL is returned.

Strings are compared lexicographically. For Boolean values, the following applies: max(TRUE, ?) = TRUE.

any min(any a, any b)

Returns the lesser of two operands. If either of the operands is null, NULL is returned.

Strings are compared lexicographically. For Boolean values, the following applies: min(FALSE, ?) = FALSE.

any safeMax(any a, any b)

Returns the greater of two operands. If either of the operands is null, then the value of the other operand is returned.

Strings are compared lexicographically. For Boolean values, the following applies: safeMax(TRUE, ?) = TRUE.

any safeMin(any a, any b)

Returns the lesser of two operands. If either of the operands is null, then the value of the other operand is returned.

Strings are compared lexicographically. For Boolean values, the following applies: safeMin(FALSE, ?) = FALSE.

If the input is of an integer type, the function returns the average value of non-null values in a group. The result is an integer number rounded down. For example, avg(2, null, 4) = 6/2 = 3.

If the input is of a float type, the function returns the average value of non-null values in a group. The result is a number of a float type not rounded. For example avg(1.5, 2.6, 3.8) = 7.9/3 = 2.633333333333333.

If the input is of a day type, the function returns the average value of non-null values in a group. The result is a day type rounded down. For example, avg(2019-10-05, 2018-11-11, 2006-12-12) = 2015-03-10.

If the input is of a daytime type, the function returns the average value of non-null values in a group. The result is a daytime type rounded down. For example, avg(2019-10-05 10:20:30, 2018-11-11 00:00:00, 2006-12-12 12:00:00) = 2015-03-10 23:06:50.

Conditional version of avg.

See also the description of avg.

string concatenate(string expression [, string separator=" "] [, integer maxLen])

Returns a concatenated string made up of non-null values in a group, separated by the value in separator (optional).

The resulting string never exceeds maxLen characters. Elements causing overflow are not added.

string concatenateif(boolean boolExpression, string expression [, string separator=" "], integer maxLen)

Conditional version of concatenate.

See also the description of concatenate.

integer count([any expression])

Returns the number of members or non-null expression values in a group. Both 32-bit and 64-bit integer data types are supported.

integer countDistinct(any expression)

Returns the number of distinct non-null values in a group.

integer countDistinctif(boolean boolExpression, any expression)

Conditional version of countDistinct.

See also the description of countDistinct.

integer countif(boolean boolExpression [, any expression])

Conditional version of count.

integer countUnique(any expression)

Returns the number of non-null values in a group that occur only one time.

integer countUniqueif(boolean boolExpression, any expression)

Conditional version of countUnique.

See also the description of countUnique.

Returns the first link of the oriented graph that creates a cycle. The function takes adjacency lists of parents and their children and performs recursive depth-first search to find cycles.

Returns NULL if a cycle is not found.

any first(any expression)

Returns the first value in a group (including null values). This aggregation value depends on the order of group members, which is given by context.

any firstif(boolean boolExpression, any expression)

Conditional version of first.

See also the description of first.

integer frequency(any expression)

The function assigns a number to each record that represents the total number of records in the same group (from the aggregation context) that share the same value of the expression argument.

The frequency function can be used to select a representative record as the one with the most or least common value of some attribute.

any last(any expression)

Returns the last value in a group (including null values). This aggregation value depends on the order of group members, which is given by context.

any lastif(boolean boolExpression, any expression)

Conditional version of last.

See also the description of last.

any maximum(any expression [, any result])

Returns the maximum of non-null values in a group. When the second argument is used, the function returns its (first non-null) value for record having maximum.

any maximumif(boolean boolExpression, any expression [, any result])

Conditional version of maximum.

See also the description of maximum.

any minimum(any expression [, any result])

Returns the minimum of non-null values in a group. When the second argument is used, the function returns its (first non-null) value for record having minimum.

any minimumif(boolean boolExpression, any expression [, any result])

Conditional version of minimum.

See also the description of minimum.

any modus(any expression [, any result])

Returns the most frequent non-null value of expression in a group or, if specified, the first non-null value of result members having the most frequent non-null value expression. In case of more than one value with the same frequency, one of the matching values is chosen arbitrarily.

any modusif(boolean boolExpression, any expression [, any result])

Conditional version of modus.

Returns the first non-null value of result members having the most frequent non-null value expression. In case of more than one value with the same frequency, one of the matching values is chosen arbitrarily.

Returns the sum of non-null values in a group.

For Boolean arguments, this function performs the logical sum (OR), for example, sum(true, true, false) = true. Both 32-bit and 64-bit integer data types are supported.

Conditional version of sum.

See also the description of sum.

any case({boolean expr, any exprValue}…​ [, any defaultExpr=null])

Returns the value of expression exprValue immediately following the first expression expr whose value is TRUE. If none of the expressions expr is evaluated as TRUE, then defaultExpr is returned if defaultExpr is specified. Otherwise, NULL is returned.

The types of all values of exprValue must be the same.

any decode(any decodeExpr {, any expr, any exprValue}…​ [, any defaultExpr=null])

Returns the value of expression exprValue immediately following the first expression expr whose value is equal to decodeExpr. If none of the expressions expr is evaluated as TRUE, then defaultExpr is returned if defaultExpr is specified. Otherwise, NULL is returned.

The types of all values of exprValue must be the same. Additionally, all types of the value of exprValue must correspond to the type of expression expr.

any iif(boolean ifExpr, any trueExpr, any elseExpr)

Returns trueExpr if ifExpr is TRUE. If ifExpr is FALSE or UNKNOWN, elseExpr is returned.

any nvl({any expr}…​)

Returns the value of the first expression expr whose value is not-null. If no such value exists, then NULL is returned.

long getMilliseconds(datetype srcExpression)

Returns internal representation of datetime value, that is, the number of milliseconds since the epoch which starts at January 1, 1970, 00:00:00 GMT. The returned value can be converted back into DAY/DATETIME using toDate or toDateTime function.

numeric [math.]abs(numeric expr)

Returns the absolute value of the expression expr.

Converts the expression expr to the nearest higher integer value.

integer [math.]floor(float expr)

Converts the expression expr to the nearest lower integer value.

Converts the expression expr to the nearest higher long value.

long [math.]longFloor(float expr)

Converts the expression expr to the nearest lower long value.

float [math.]round(float expr [, integer decimalPlaces=0])

Rounds the expression expr to a given number of decimal places, specified by decimalPlaces.

Returns the date specified in the expression expr converted to date type DAY. If the conversion is not successful, then NULL is returned.

If a single argument is used, expr can be DAY or LONG expression. If expr is a STRING value, its format is defined by the dateFormat parameter (of type STRING) and the localization defined in the dateLocale parameter (of type STRING). The dateFormat and dateLocale strings depend on the classes SimpleDateFormat and Locale.

For example, if the date in string format is '30/05/2023', then the conversion is toDateTime('30/05/2023','dd/MM/yyyy'). You can also convert a date to string using the toString expression toString(date_col, 'yyyy-MM-dd') to get 2023-05-30 date.

Returns the date specified in the expression expr converted to date type DATETIME. If the conversion is not successful, then NULL is returned.

If a single argument is used, expr can be DAY or LONG expression. If expr is a STRING value, its format is defined by the dateFormat parameter (of type STRING) and the localization defined in the dateLocale parameter (of type STRING). The dateFormat and dateLocale strings depend on the classes SimpleDateFormat and Locale.

For example, if the date in string format is '30/05/2023 15:00:00', then the conversion is toDateTime('30/05/2023 15:00:00','dd/MM/yyyy HH:mm:ss'). You can also convert a date to string using the toString expression toString(date_col, 'yyyy-MM-dd HH:mm:ss') to get 2023-05-30 15:00:00 date.

Converts the expression expr to a FLOAT value. If the conversion is not successful, then NULL is returned.

integer toInteger(string expr)

Converts the expression expr to an INTEGER value. If the conversion is not successful, then NULL is returned.

Converts the expression expr to a LONG value. If the conversion is not successful, then NULL is returned.

Converts the expression expr to a STRING value. If the conversion is not successful, then NULL is returned.

The parameter strFormat is required for expressions of type DATETIME (see the toDateTime function) or DAY. When only the expr parameter is set, then the default Java convert method (toString) is used for the conversion.

If the parameter strFormat is set, then it is used as the output format. If strLocale is not set, the default locale for the JVM instance is used.

If the strFormat parameter (and optionally, strLocale) is set, then only expressions of date or numeric types (DATETIME, DAY, INTEGER, LONG or FLOAT) can be converted. Conversions for other types with parameters strFormat and, optionally, strLocale specified are not defined. The strFormat and strLocale strings depend on the classes SimpleDateFormat and Locale.

string xpath(string xml, string xpath)

Retrieves the XML element from the source XML string on the specified XPath.

integer [set.]approxSymmetricDifference(string set1, string set2 [, string separator=" "] [, boolean multiset=false] [, integer tolerance] [, string diff_function] [, integer singularity])

Returns the number of words which cannot be matched with any word in the other set. The matching is based on one of difference functions, 'levenshtein' (default), 'editDistance', or 'hamming', and the tolerance as the maximum number of changes in a word. If the tolerance is specified as a non-integer number less than 1 and greater than 0, the means is relative - the ratio of the number of changes divided by the length of longer word.

boolean set.contains(string str, string item [, string separator=" "])

Checks whether the item is contained in the string set.

boolean set.containsExp(string str, string item [, string separator=" "], function comparator)

Checks whether the item is contained in the string set. The elements are compared according to the specified comparator.

integer [set.]difference(string set1, string set2 [, string separator=" "] [, boolean multiset=false] [, integer singularity])

Returns the cardinality of the difference of sets (set1 \ set2).

integer set.differenceExp(string set1, string set2 [, string separator=" "] [, boolean multiset=false] [, integer singularity], function comparator)

Returns the cardinality of the difference of sets (set1 \ set2).

string set.differenceResult(string set1, string set2 [, string separator=" "] [, boolean multiset=false])

Returns the difference of sets (set1 \ set2).

string set.differenceResultExp(string set1, string set2 [, string separator=" "] [, boolean multiset=false], function comparator)

Returns the difference of sets (set1 \ set2).

string set.distinct(string str [, string separator=" "])

Returns the set of distinct elements from the source string.

string set.distinctExp(string str [, string separator=" "], function comparator)

Returns the set of distinct elements from the source string. The elements are compared according to the specified comparator.

string set.filterExp(set, string separator=" ", function callback)

Returns the string set containing elements for which the Boolean functional parameter is true. Equivalent to set.mapExp(set, separator, (x) { iif(function(x), x, "") }).

For example, filterExp('John W. Smith', ' ', (x) { length(x) > 2 }) returns "John Smith".

integer set.indexOf(string str, string item [, string separator=" "])

Returns the position of item in the string set. If the item is not present, it returns NULL.

integer set.indexOfExp(string str, string item [, string separator=" "], function comparator)

Returns the position of item in the string set. If the item is not present, it returns NULL. The elements are compared according to the specified comparator.

integer [set.]intersection(string set1, string set2 [, string separator=" "] [, boolean multiset=false])

Returns the cardinality of the intersection of sets.

integer set.intersectionExp(string set1, string set2 [, string separator=" "] [, boolean multiset=false], function comparator)

Returns the cardinality of the intersection of sets.

string set.intersectionResult(string set1, string set2 [, string separator=" "] [, boolean multiset=false])

Returns the intersection of sets.

string set.intersectionResultExp(string set1, string set2 [, string separator=" "] [, boolean multiset=false], function comparator)

Returns the intersection of sets.

string set.item(string str, integer index [, string separator=" "])

Returns the element of the string set at the specified position, including empty elements.

integer set.lastIndexOf(string str, string item [, string separator=" "])

Returns the position of the last item in the string set. If the item is not present, it returns NULL.

integer set.lastIndexOfExp(string str, string item [, string separator=" "], function comparator)

Returns the position of the last item in the string set. If the item is not present, it returns NULL. The elements are compared according to the specified comparator.

integer set.lcsDifference(string set1, string set2 [, string separator=" "] [, boolean multiset=false] [, integer singularity])

Returns the cardinality of the difference of sets (set1 \ set2) (lcs-based intersection).

integer set.lcsDifferenceExp(string set1, string set2 [, string separator=" "] [, boolean multiset=false] [, integer singularity], function comparator)

Returns the cardinality of the difference of sets (set1 \ set2) (lcs-based intersection).

string set.lcsDifferenceResult(string set1, string set2 [, string separator=" "] [, boolean multiset=false])

Returns the difference of sets (set1 \ set2) (lcs-based intersection).

string set.lcsDifferenceResultExp(string set1, string set2 [, string separator=" "] [, boolean multiset=false], function comparator)

Returns the difference of sets (set1 \ set2) (lcs-based intersection).

integer set.lcsIntersection(string set1, string set2 [, string separator=" "] [, boolean multiset=false])

Returns the cardinality of the intersection of sets (lcs-based intersection).

integer set.lcsIntersectionExp(string set1, string set2 [, string separator=" "] [, boolean multiset=false], function comparator)

Returns the cardinality of the intersection of sets (lcs-based intersection).

string set.lcsIntersectionResult(string set1, string set2 [, string separator=" "] [, boolean multiset=false])

Returns the intersection of sets (lcs-based intersection).

string set.lcsIntersectionResultExp(string set1, string set2 [, string separator=" "] [, boolean multiset=false], function comparator)

Returns the intersection of sets (lcs-based intersection).

integer set.lcsSymmetricDifference(string set1, string set2 [, string separator=" "] [, boolean multiset=false] [, integer singularity])

Returns the cardinality of the symmetric difference of sets (lcs-based intersection).

integer set.lcsSymmetricDifferenceExp(string set1, string set2 [, string separator=" "] [, boolean multiset=false] [, integer singularity], function comparator)

Returns the cardinality of the symmetric difference of sets (lcs-based intersection).

string set.lcsSymmetricDifferenceResult(string set1, string set2 [, string separator=" "] [, boolean multiset=false])

Returns the symmetric difference of sets (lcs-based intersection).

string set.lcsSymmetricDifferenceResultExp(string set1, string set2 [, string separator=" "] [, boolean multiset=false], function comparator)

Returns the symmetric difference of sets (lcs-based intersection).

string set.mapExp(set [, string separator=" "], function callback)

Returns a string set whose each element is transformed by the functional parameter. For example, mapExp('John Walker Smith', ' ', (x) { left(x,3) }) returns "Joh Wal Smi".

integer set.size(string str [, string separator=" "])

Returns the number of elements in the string, including empty elements.

string set.sort(string str [, string separator=" "] [, string locale=null] [, boolean desc=false])

Returns a sorted set of elements.

string set.subSequence(string str, integer from [, integer count=null] [, string separator=" "])

Returns elements of the string set starting at the specified position, including empty elements.

The from index can be negative, in which case the starting index is assumed from the last (set.subSequence("A B C D", -3, 2) == "B C"). When the`count` is not specified or null, all remaining elements are selected (set.subSequence("A B C D", -3) == "B C D").

numeric set.sumExp(set [, string separator=" "], function callback)

Returns the sum of values calculated by the functional parameter for each element. The functional parameter expression must return an integer, long, or float result.

For example, sumExp('John Walker Smith', ' ', (x) { length(x) }) returns 4 + 6 + 5 = 15. The result for an empty set (null or an empty string in the first argument) is 0.

integer [set.]symmetricDifference(string set1, string set2 [, string separator=" "] [, boolean multiset=false] [, integer singularity])

Returns the cardinality of the symmetric difference of sets.

integer set.symmetricDifferenceExp(string set1, string set2 [, separator=" "] [, boolean multiset=false] [, integer singularity], function comparator)

Returns the cardinality of the symmetric difference of sets.

string set.symmetricDifferenceResult(string set1, string set2 [, string separator=" "] [, boolean multiset=false])

Returns the symmetric difference of sets.

string set.symmetricDifferenceResultExp(string set1, string set2 [, string separator=" "] [, boolean multiset=false], function comparator)

Returns the symmetric difference of sets.

Example:

integer [set.]union(string set1, string set2 [, string separator=" "] [, boolean multiset=false])

Returns the cardinality of the union of sets.

integer set.unionExp(string set1, string set2 [, string separator=" "] [, boolean multiset=false], function comparator)

Returns the cardinality of the union of sets.

string set.unionResult(string set1, string set2 [, string separator=" "] [, boolean multiset=false])

Returns the union of sets.

string set.unionResultExp(string set1, string set2 [, string separator=" "] [, boolean multiset=false], function comparator)

Returns the union of sets.