Lead your team forward
OCT 24 / 9AM ET Register nowONE Expressions
This article describes expressions used within ONE steps. Places where the expressions can be used are mentioned in the description sections of the appropriate steps.
Operands
An operand is any expression with a type corresponding to a valid type for a given operation. Operands can be divided into four categories:

Literals  Numeric, string, or logical constants (
TRUE
,FALSE
,UNKNOWN
(deprecated), all keywords are caseinsensitive) and null literal (caseinsensitive). 
Columns  Defined by their names. If there is a space character in the column name, the name must be enclosed in square brackets (
[]
). If the step retrieves data from multiple inputs, the column names are specified using the dot notation format, that is,input_name.column_name
. If the step uses only one input, the dot notation may be omitted. 
Set  Can be used only in combination with the
IN
operation where the set represents a constant expression. A set can only occur on the right side of theIN
operation. 
Complex expressions.
Expression operands can be of a defined column type, such as INTEGER
, FLOAT
, LONG
, STRING
, DATETIME
, DAY
, and BOOLEAN
.
If a number assigned to either an INTEGER
or LONG
variable overflows or underflows the interval of permitted values for that type (that is, 2147483648;+2147483647
for INTEGER
and 9223372036854775808;+9223372036854775807
for LONG
,) then the number wraps around the interval.
For example, the value 2147483649
assigned to an INTEGER
variable is interpreted as 2147483647
.
Operands are automatically converted to a wider type if needed.
This is relevant for numeric data types INTEGER
, LONG
, and FLOAT
(widening INTEGER
⇒ LONG
⇒ FLOAT
) and datetime types DAY
and DATETIME
(DAY
⇒ DATETIME
).
In case of comparisons, set, and conditional operations, all operands are first converted to the most general type and then the operation is performed.
Handling null values
Operations and functions handle arguments with null value according to SQL rules.
One important exception is for STRING
data type.
A null string and an empty string are considered equal, so null string arguments are handled as empty (zero length) strings.
For example, "abc" == NULL
, or even "`abc" > NULL`, is a legal comparison and gives nonnull Boolean result analogous to "abc" == ""
, or "abc" > ""
respectively, whereas in SQL language these expressions give both null (UNKNOWN
) value.
Variables
The expression can be formed as a sequence of assignment expressions followed by one resulting expression.
Multiple expressions are delimited by a semicolon (;
).
Assignment expressions have the following syntax: variable := expression
.
The first occurrence of a variable on the lefthand side defines this variable and its type. A reference to a variable in an expression is valid only after its definition. Each following occurrence of a variable, including on the lefthand side of an assignment expression, must conform to the variable’s type.
a := 2;
b := 4  a;
3 * b
Operations and functions
ONE provides the following function and operation categories:

Arithmetic operations

Logical operations

Comparison operations

Set operations

Other operations

Date functions

String functions

Bitwise functions

MinMax functions

Aggregating functions

Conditional expressions

Conversion and formatting functions

Word set operation functions
All functions and operations that do not have the "locale" parameter set or defined are set to use the default ONE locale.
The step locale setting does not influence this behavior.

Arithmetic operations
This category includes common arithmetic operations—addition, subtraction, multiplication, and division.
The result of an arithmetic operation applied to the types INTEGER
and/or LONG
is always INTEGER
or LONG
.
The result is of type LONG
if at least one operand was of type LONG
.
The type NUMBER in the description of input and output types stands for data types INTEGER , LONG , or FLOAT .

Operator  Usage  Description  

 

Subtraction of numeric operands 

 

Negation of numeric operand


/ 

Division of numeric operands 

* 

Multiplication of numeric operands 

% 

Modulo: the remainder after numerical division of 

+ 

Addition of numeric operands 

div 

Division of integer operands without a remainder. 
Logical operations
Common logical operations AND, OR, XOR, and NOT (all keywords are caseinsensitive).
Operator  Usage  Description 

AND 

Logical conjunction. 
NOT 

Logical negation. 
OR 

Logical sum. 
XOR 

Exclusive OR. 
Comparison operations
Relational operators.
Operator  Usage  Description 

< 

Tests if the value 
<= 

Tests if the value 
<>, != 

Tests the negated equivalence of two values. 
=, == 

Tests the equivalence of two values. 
> 

Tests if the value 
>= 

Tests if the value 
Set operations
For sets, a few basic operationsIN
, IS IN
, NOT IN
, IS NOT IN
 are implemented.
Set members are literals of types defined for columns or column names themselves.
Operator  Usage  Description 

in 

Tests whether 
is in 

Tests whether 
is not in 

Tests whether 
not in 

Tests whether 
company IN {"Smith inc.", "Smith Moving inc.",
"Speedmover inc.", [candidate column], clear_column}
a IN {1, 2, 5, 10}
b IN {TRUE, FALSE}
Other operations
Function  Usage  Description  

getParameterValue 
string getParameterValue(string key [, string default]) 
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:


getRuntimeVersion 
string getRuntimeVersion() 
The function returns the product version. 

is 
a is b 
Tests if A typical use is 

is not 
a is not b 
Tests if A typical use is 

setParameterValue 
string setParameterValue(string key, string value) 
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 Examples are: 

geoDistance 
float geoDistance(float lat1, float long1, float lat2, float long2) 
Calculates the shortest distance (in meters) between two points on Earth. A point is defined by its latitude and longitude in degrees.


namedSequence 
integer sequence(string name [, integer start= 
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 Otherwise, it takes the already created counter object, increments the value of the object by the If there are more named sequences with different 

random 
integer random([[integer from= 
Generates a random number from the interval defined by the parameters The The default values are 

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

sequence 
integer sequence(integer [, start= 
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 Otherwise, it takes the already created counter object, increments the value of the object by the The default values are 
Date functions
In ONE, a date is represented by DAY
and DATETIME
types.
The DAY
type represents a date to the detail level of days, DATETIME
to the detail level of milliseconds.
The time values that are compatible with each format are named and described in the following table.
Date part name  Range  Included in date type 


Any positive number 


1  12 


1  max.month 


0  23 


0  59 


0  59 

A day starts at 00:00:00 and ends at 23:59:59. If a given function requires identification of a date part as parameter, the identifier is written in the expression in the form of a string literal, for example, "MONTH", otherwise the expression is evaluated as incorrect.
Identifiers are case sensitive and must be written in uppercase.
For example: expression='dateAdd(inDate,10,"DAY")'
.
All the listed date parts are represented by positive integers. Milliseconds are not supported by the date functions.
Date type DATETYPE stands for date types DAY or DATETIME in the description of input and output types.

Function  Usage  Description 

dateAdd 
datetype dateAdd(datetype srcDate, integer srcValue, string fieldName) 
Adds the specified This function allows subtraction, so the If any of the operands are invalid or if an unsupported 
dateDiff 
integer dateDiff(datetype startDate, datetype endDate, string fieldName) 
Returns the difference between If the result exceeds the maximum range of A combination of date type 
datePart 
integer datePart(datetype srcDate, string fieldName) 
This function returns the value of the field If any of the parameters are invalid, the expression reports an error.
For the fields 
dateTrunc 
datetype dateTrunc(datetype srcDate, string fieldName) 
The function truncates less important parts of the The function can be used even for the If any of the parameters are invalid, the expression reports an error. Example: for 
getDate 
day getDate(datetype srcExpression) 
Returns the date in the format defined by the specified srcExpression (type 
getRequestTime 
datetime getRequestTime() 
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. 
now 
datetime now() 
Returns the current time with the type 
today 
day today() 
Returns the current date in type 
String functions
The following are common functions used for string processing.
Function  Usage  Description  

capitalize 
string capitalize(string srcStr) 
Transforms all words in the string A word consists of alphabetic characters (letters). All other characters are considered separators. 

capitalizeWithException 
string capitalizeWithException(string srcStr [, boolean leaveExcsAsTheyAre= 
Transforms all words in the string A word consists of alphabetic characters (letters). All other characters are considered separators. A current downside is that words from 

containsWord 
boolean containsWord(string srcStr, string srcWord [, string separator=" "]) 
Searches for the occurrence of the word A word is a sequence of letters with no whitespaces.
Words in the string are defined as sequences of letters separated by a space (


countNonAsciiLetters 
integer countNonAsciiLetters(string srcStr) 
Returns the number of characters from the string 

cpConvert 
string cpConvert(string srcStr, string actualCp, string correctCp) 
Takes a string as an input wrongly read using the For example, if you have a file that is entirely in 

diceCoefficient 
float diceCoefficient(string srcStr1, string srcStr2 [, string tokenSeparator= 
Returns the The method uses a set similarity measure called SørensenDice coefficient.
It uses the following formula: 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:


distinct 
string distinct(string srcStr, null) 
Returns a string which contains the concatenated first occurrence of parts of the original string 

doubleMetaphone 
string doubleMetaphone(string srcStr [, boolean isAlternate= 
Encodes If 

editDistance 
integer editDistance(string srcStr1, string srcStr2 [, integer maxDistance] [, boolean caseInsensitive= 
Returns the edit distance between strings The parameter The parameter 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 This function is also known as DamerauLevenshtein distance. 

eraseSpacesInNames 
string eraseSpacesInNames(string srcStr, integer minLength, boolean onlyUpper) 
Removes spaces between separate characters (words of length 1``) in the string The parameter The parameter 

find 
boolean find(string srcRegex, string srcStr [, boolean caseInsensitive= 
Verifies whether the string The parameter If the string 

hamming 
integer hamming(string srcStr1, string srcStr2 [, boolean caseInsensitive= 
Returns the Hamming distance between strings The parameter If both of the strings are null, then the result is 

indexOf 
integer indexOf(string srcStr, string subStr [, integer fromIndex= 
Returns the index within the string If the substring is not found, the value If the value The index of the first character is 

isInFile 
boolean isInFile(string srcStr, string fileName) 
Searches for the string The function returns 

isNumber 
boolean isNumber(string srcStr) 
Verifies whether the string 

jaccardCoefficient 
float jaccardCoefficient(string srcStr1, string srcStr2 [, string tokenSeparator= 
Returns the The method uses a set similarity measure called Jaccard coefficient.
It uses the following formula: 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:


jaroWinkler 
float jaroWinkler(string srcStr1, string srcStr2 [, boolean caseInsensitive= 
Returns the JaroWinkler score for strings This is a combination of Jaro’s algorithm with Winkler’s boost for identical prefixes. For an extensive description of the calculation, see JaroWinkler distance. To turn off the Winkler’s prefix boost (obtain pure Jaro), set the boost threshold to a value greater than 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 

lastIndexOf 
integer lastIndexOf(string srcStr, string subStr [, integer fromIndex= 
Returns the index within the string If the substring is not found, the value If the value The index of the first character is 

left 
string left(string srcStr, integer count) 
Returns a new string that is a substring of 

length 
integer length(string srcStr) 
Returns the number of characters in the string srcStr. 

levenshtein 
integer levenshtein(string srcStr1, string srcStr2 [, integer maxDistance] [, boolean caseInsensitive= 
Returns the Levenshtein distance between strings The parameter The parameter If both of the strings are null, then the result is 

lower 
string lower(string srcStr) 
Transforms all characters of the string srcStr to lowercase. 

matches 
boolean matches(string srcRegex, string srcStr [, boolean caseInsensitive= 
Verifies whether the string The parameter If the string For information about regular expressions, Regular expressions. 

metaphone 
string metaphone(string srcStr) 
Encodes the string 

ngram 
string ngram(string srcStr, string inSeparator, string outSeparator, integer size) 
Takes`srcStr` as a sequence of items separated by Ngrams in the output sequence are separated by The length of both separators as well as of the ngrams has to be at least If there are not enough words for creating an ngram, the function returns 

preserveCase 
string preserveCase(string srcStr, string maskStr) 
Transforms all characters in 

removeAccents 
string removeAccents(string srcStr) 
Returns a copy of the string 

replace 
string replace(string srcStr, string what, string withWhat) 
Replaces occurrences of the string The function returns 

replicate 
string replicate(string srcStr, integer n) 
Returns 

right 
string right(string srcStr, integer count) 
Returns a new string that is a substring of 

sortWords 
string sortWords(string srcStr [, string srcLocale= 
Returns a string that consists of sorted parts of the string The parameter If the Boolean parameter If the string 

soundex 
string soundex(string srcStr) 
Returns the soundex value of the 

squeezeSpaces 
string squeezeSpaces(string srcStr) 
Removes whitespace characters from both ends of the string 

substituteAll 
string substituteAll(string srcPattern, string srcReplacement, string srcStr [, boolean caseInsensitiveFlag= 
Replaces all occurrences of If the parameter 

substituteMany 
string substituteMany(string srcPattern, string srcReplacement, string srcStr, integer srcVolume [, boolean caseInsensitiveFlag= 
Replaces all occurrences of If the parameter 

substr 
string substr(string srcStr, integer beginIndex [, integer strLen]) 
Returns a new string that is a substring of the string If If An empty substring is returned as a The index of the first character is 

transliterate 
string transliterate(string srcStr, string charsFrom, string charsTo) 
Transforms characters of the string The transformation replaces all occurrences of any character named in the parameter 

trashConsonants 
string trashConsonants(string srcStr) 
Removes all consonants (only Latin letters are concerned) and their accented equivalents from the string 

trashDiacritics 
Deprecated, replaced by the function 

trashNonDigits 
string trashNonDigits(string srcStr) 
Returns a string that consists only of the digits included in the original string 

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

trashVowels 
string trashVowels(string srcStr) 
Removes all vowels (only Latin letters are concerned) and their accented equivalents from the string 

trim 
string trim(string srcStr [, string fillChars]) 
Removes whitespace or specified characters from both ends of the string For more information, see the trim method of the class 

trimLeft 
string trimLeft(string srcStr [, string fillChars]) 
Removes whitespace or specified characters from the left side of the string For more information, see the trim method of the class 

trimRight 
string trimRight(string srcStr [, string fillChars]) 
Removes whitespace or specified characters from the right side of the string For more information, see the trim method of the class 

upper 
string upper(string srcStr) 
Transforms all characters of the string 

word 
string word(string srcStr, integer srcIdx [, string srcSeparator= 
Returns the Words are defined as sequences of letters separated by The index of the first word is 

wordCombinations 
string wordCombinations(string srcStr, integer size, string srcSeparator, string combSeparator) 
Returns all Words are defined as sequences of characters separated by The words in the combination are separated by If any of the arguments is null or 

wordCount 
integer wordCount(string srcStr [, string srcSeparator= 
Returns the number of nonempty words in the string Words are defined as sequences of characters separated by 
Coding functions
Functions for hashing, encoding, or decoding strings.
Function  Usage  Description 

coding.fromBase64 
string coding.fromBase64(string source) 
Decodes the source string from Base64. 
coding.HMacMD5 
string coding.HMacMD5(string message, string key) 
Creates a hashbased message authentication code using MD5 algorithm. 
coding.HMacSHA1 
string coding.HMacSHA1(string message, string key) 
Creates a hashbased message authentication code using SHA1 algorithm. 
coding.HMacSHA512 
string coding.HMacSHA512(string message, string key) 
Creates a hashbased message authentication code using SHA512 algorithm. 
coding.htmlEscape 
string coding.htmlEscape(string source, boolean allEntities) 
Converts XML/HTML special characters ( If set to 
coding.htmlUnescape 
string coding.htmlUnescape(string source, boolean allEntities) 
Transforms entities representing XML/HTML special characters ( The entity 
coding.md5 
string coding.md5(string source) 
Creates a hash of the source string using MD5 algorithm. 
coding.toBase64 
string coding.toBase64(string source) 
Encodes the source string to Base64. 
coding.urlDecode 
string coding.urlDecode(string source, string charset) 
Decodes a URLencoded string, converting hexadecimal byte representations ( 
coding.urlEncode 
string coding.urlEncode(string source, string charset) 
Encodes a string for safe URL use by converting unsupported characters into their hexadecimal ( 
encode.base64 
Deprecated, use 

encode.HMacMD5 
Deprecated, use 

encode.HMacSHA1 
Deprecated, use 

encode.HMacSHA512 
Deprecated, use 

encode.md5 
Deprecated, use 
Math functions
Traditional mathematical functions. They return float value and usually have one float argument.
Function  Usage  Description 

math.acos 
float math.acos(float x) 
Arc cosine of x.
Defined for 
math.asin 
float math.asin(float x) 
Arc sine of x.
Defined for 
math.atan 
float math.atan(float x) 
Arc tangent of x. 
math.cos 
float math.cos(float x) 
Cosine of x. The argument is assumed in radians. 
math.e 
float math.e() 
Returns Euler’s number (e) = 2,7182818285. 
math.exp 
float math.exp(float x) 
Exponential function ex.
Equivalent to 
math.log 
float math.log(float x) 
Natural logarithm of x.
Defined for 
math.log10 
float math.log10(float x) 
Common logarithm of x.
Defined for 
math.pi 
float math.pi() 
Returns π (PI) = 3,1415926536. 
math.pow 

Returns x power to y.
The function is undefined and returns 
math.sin 
float math.sin(float x) 
Sine of x. The argument is assumed in radians. 
math.sqr 
numeric math.sqr(numeric x) 
Returns square of x. 
math.sqrt 
float math.sqrt(float x) 
Square root of x.
Defined for 
math.tan 
float math.tan(float x) 
Tangent of x. The argument is assumed in radians. Undefined for π/2 + k*π. 
Bitwise functions
Bitwise functions are logical operations applied on separate bits of the operands.
Function  Usage  Description 

bitand 

Bitwise AND 
bitneg 

Bitwise NOT, or complement 
bitor 

Bitwise inclusive OR 
bitxor 

Bitwise exclusive or 
MinMax functions
Functions used for computation of minimum or maximum values.
Function  Usage  Description 

max 
any max(any a, any b) 
Returns the greater of two operands.
If either of the operands is null, Strings are compared lexicographically.
For Boolean values, the following applies: 
min 
any min(any a, any b) 
Returns the lesser of two operands.
If either of the operands is null, Strings are compared lexicographically.
For Boolean values, the following applies: 
safeMax 
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: 
safeMin 
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: 
Aggregating functions
Aggregating functions are special functions that can be used only in the context of ONE steps that support grouping of records. Currently, there are two such steps: Representative Creator and Group Aggregator.
Depending on the context, expressions containing aggregate functions distinguish between two types of sources: inner, used in arguments of any aggregated function, and outer, used outside of functions. These might be generally different, for example when the sum of a certain attribute of all records in a group is added to another attribute of a record that has an entirely different format and usage.
Every aggregating function has a variant for conditional evaluation.
The name of the variant is derived from the original name with the appended suffix if
.
The conditional variant has one extra argument that is inserted before the original arguments and contains a Boolean expression.
The expression specifies when the appropriate record is included into aggregation.
For example, the expression avg(salary)
can have the conditional variant avgif(score < 100, salary)
.
Nesting of aggregate functions is not allowed.
For example, the expression countif(salary < avg(salary)) is invalid.

Function  Usage  Description  

avg 

If the input is of an integer type, the function returns the average value of nonnull values in a group.
The result is an integer number rounded down.
For example, If the input is of a float type, the function returns the average value of nonnull values in a group.
The result is a number of a float type not rounded.
For example If the input is of a day type, the function returns the average value of nonnull values in a group.
The result is a day type rounded down.
For example, If the input is of a daytime type, the function returns the average value of nonnull values in a group.
The result is a daytime type rounded down.
For example, 

avgif 

Conditional version of See also the description of 

concatenate 
string concatenate(string expression [, string separator= 
Returns a concatenated string made up of nonnull values in a group, separated by the value in The resulting string never exceeds 

concatenateif 
string concatenateif(boolean boolExpression, string expression [, string separator= 
Conditional version of See also the description of 

count 
integer count([any expression]) 
Returns the number of members or nonnull expression values in a group. Both 32bit and 64bit integer data types are supported. 

countDistinct 
integer countDistinct(any expression) 
Returns the number of distinct nonnull values in a group. 

countDistinctif 
integer countDistinctif(boolean boolExpression, any expression) 
Conditional version of See also the description of 

countif 
integer countif(boolean boolExpression [, any expression]) 
Conditional version of 

countUnique 
integer countUnique(any expression) 
Returns the number of nonnull values in a group that occur only one time. 

countUniqueif 
integer countUniqueif(boolean boolExpression, any expression) 
Conditional version of See also the description of 

detectCycles 

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 depthfirst search to find cycles. Returns 

first 
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. 

firstif 
any firstif(boolean boolExpression, any expression) 
Conditional version of See also the description of 

frequency 
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 The frequency function can be used to select a representative record as the one with the most or least common value of some attribute.


last 
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. 

lastif 
any lastif(boolean boolExpression, any expression) 
Conditional version of See also the description of 

maximum 
any maximum(any expression [, any result]) 
Returns the maximum of nonnull values in a group. When the second argument is used, the function returns its (first nonnull) value for record having maximum. 

maximumif 
any maximumif(boolean boolExpression, any expression [, any result]) 
Conditional version of See also the description of 

minimum 
any minimum(any expression [, any result]) 
Returns the minimum of nonnull values in a group. When the second argument is used, the function returns its (first nonnull) value for record having minimum. 

minimumif 
any minimumif(boolean boolExpression, any expression [, any result]) 
Conditional version of See also the description of 

modus 
any modus(any expression [, any result]) 
Returns the most frequent nonnull value of 

modusif 
any modusif(boolean boolExpression, any expression [, any result]) 
Conditional version of Returns the first nonnull value of result members having the most frequent nonnull value 

sum 

Returns the sum of nonnull values in a group. For Boolean arguments, this function performs the logical sum (OR), for example, 

sumif 

Conditional version of See also the description of 
Conditional expressions
Conditional expressions are a special type of expressions where the resulting value depends on the evaluation of certain conditions. These functions do not have strictly defined argument types, instead they are flexible and their arguments are defined by the specific functionality of each expression.
Function  Usage  Description 

case 
any case({boolean expr, any exprValue}… [, any defaultExpr= 
Returns the value of expression The types of all values of 
decode 
any decode(any decodeExpr {, any expr, any exprValue}… [, any defaultExpr= 
Returns the value of expression The types of all values of 
iif 
any iif(boolean ifExpr, any trueExpr, any elseExpr) 
Returns 
nvl 
any nvl({any expr}…) 
Returns the value of the first expression 
case (
id is null, "_" + input + "_",
id = 1, substr(input, length(input) / 2),
"default value"
)
decode (
id,
0,
'zero',
1,
'one',
2,
'two',
3,
'three'
)
iif (
value == 2,
'ok',
'bad'
)
nvl (
value1,
value2,
value3
)
Conversion and formatting functions
Conversion functions are used for conversions and formatting the input expression.
Function  Usage  Description 

getMilliseconds 
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 
math.abs 
numeric [math.]abs(numeric expr) 
Returns the absolute value of the expression 
math.ceil, math.ceiling 

Converts the expression 
math.floor 
integer [math.]floor(float expr) 
Converts the expression 
math.longCeil, math.longCeiling 

Converts the expression expr to the nearest higher long value. 
math.longFloor 
long [math.]longFloor(float expr) 
Converts the expression 
math.round 
float [math.]round(float expr [, integer decimalPlaces= 
Rounds the expression 
toDate 

Returns the date specified in the expression expr converted to date type If a single argument is used, For example, if the date in string format is '30/05/2023', then the conversion is 
toDateTime 

Returns the date specified in the expression If a single argument is used, For example, if the date in string format is '30/05/2023 15:00:00', then the conversion is 
toFloat 

Converts the expression 
toInteger 
integer toInteger(string expr) 
Converts the expression expr to an INTEGER value. If the conversion is not successful, then NULL is returned. 
toLong 

Converts the expression 
toString 

Converts the expression The parameter If the parameter If the 
XML functions
Functions for working with XML formatted data.
Function  Usage  Description 

xpath 
string xpath(string xml, string xpath) 
Retrieves the XML element from the source XML string on the specified XPath. 
Word set operation functions
Word set operation functions operate on two strings, interpreting them as sets of words separated by the given separator (or a space, by default) and return the integer cardinality of the resulting set or, in some cases, the resulting set itself.
If the parameter multiset
is set to TRUE
, the sets are treated as "multisets".
For example, two identical words in one set form two members of the set rather than one.
When multiset
is FALSE
(default), identical words are first deduplicated.
Three of the functions (intersection
, difference
, symmetricDifference
) have variants with the prefix lcs
(for example, lcsIntersection
).
These variants are based on the concept that the intersection of two (word) lists is defined as the Longest Common Subsequention.
In this case, the order or duplicity of elements in the lists is significant.
For example, two lists a = 'C A D B A'
and b = 'A E B C D'
, whose "ordinary" intersection is set 'A B C D'
, have the "lcs" intersection 'A B'
(or 'C D'
, 'A D'
), and so lcsIntersection(a, b) = 2
, lcsDifference(a, b) = 2
(or 3
in the multiset case), lcsSymmetricDifference(a, b) = 5
(or 6
).
The two types of difference functions can be executed with an optional integer parameter, singularity
, which distinguishes sets that have common members from sets without common members.
When this parameter is used, the function returns a value (typically a very large number) when the sets have an empty intersection.
For example:
* difference('A B', 'C D') = 2
: The typical result.
The difference between completely different sets might have the same value as the difference between, for example, very similar sets 'A B C D E'
and 'A B C F G'
.
* difference('A B', 'C D', 1000) = 1000
: Using the singularity
parameter produces a different result, which shows that the difference between completely different sets is high.
Most of the functions have a variant with the suffix Exp
(for example, intersectionExp
).
These variants have one appended optional argument, comparator
.
This argument is a functional parameter and it defines how the elements in sets are compared to determine if they are the same.
The functional parameter must take two string values and return a Boolean result.
The result must not depend on the argument order.
The functional argument in general is similar to the function definition and has the following syntax:
(param1, param2, ..) { expression }
The columns and variables can be accessed in the function body.
When using the functional comparator, the calculation of intersection of two sets is quite different from an "ordinary" set operation, because we cannot assume the comparator conforms to the equality relation. In both set operands A respectively B, we determine subsets A' respectively B' of members that have at least one matching member in the opposite set. Since these subsets might have different sizes, the size of the intersection is defined as the first one.
Examples of using these functional parameters:

symmetricDifference(std_name, std_hh_names, ' ', multi)
: Ordinary comparison. 
symmetricDifferenceExp(std_name, std_hh_names, ' ', multi, (x,y) { x == y })
: Ordinary comparison, equivalent to the nonExp
behavior. 
symmetricDifferenceExp(std_name, std_hh_names, ' ', multi, (x,y) { editDistance(x,y) < 2 })
: Approximative matching, elements different at most in one error are assumed to be equal.
When std_name = "AAA BBB AAA ABC"
and std_hh_names = "AAB AAA CCC BBC"
and multi = TRUE
, then the first case gives 6
(one pair of AAA
), the second one is 6
(all three AAA
paired), and the third one is 1
(only CCC
remains unpaired).
Function  Usage  Description  

set.approxSymmetricDifference 
integer [set.]approxSymmetricDifference(string set1, string set2 [, string separator= 
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


set.contains 
boolean set.contains(string str, string item [, string separator= 
Checks whether the 

set.containsExp 
boolean set.containsExp(string str, string item [, string separator= 
Checks whether the 

set.difference 
integer [set.]difference(string set1, string set2 [, string separator= 
Returns the cardinality of the difference of sets ( 

set.differenceExp 
integer set.differenceExp(string set1, string set2 [, string separator= 
Returns the cardinality of the difference of sets ( 

set.differenceResult 
string set.differenceResult(string set1, string set2 [, string separator= 
Returns the difference of sets ( 

set.differenceResultExp 
string set.differenceResultExp(string set1, string set2 [, string separator= 
Returns the difference of sets ( 

set.distinct 
string set.distinct(string str [, string separator= 
Returns the set of distinct elements from the source string. 

set.distinctExp 
string set.distinctExp(string str [, string separator= 
Returns the set of distinct elements from the source string.
The elements are compared according to the specified 

set.filterExp 
string set.filterExp(set, string separator= 
Returns the string set containing elements for which the Boolean functional parameter is For example, 

set.indexOf 
integer set.indexOf(string str, string item [, string separator= 
Returns the position of 

set.indexOfExp 
integer set.indexOfExp(string str, string item [, string separator= 
Returns the position of 

set.intersection 
integer [set.]intersection(string set1, string set2 [, string separator= 
Returns the cardinality of the intersection of sets. 

set.intersectionExp 
integer set.intersectionExp(string set1, string set2 [, string separator= 
Returns the cardinality of the intersection of sets. 

set.intersectionResult 
string set.intersectionResult(string set1, string set2 [, string separator= 
Returns the intersection of sets. 

set.intersectionResultExp 
string set.intersectionResultExp(string set1, string set2 [, string separator= 
Returns the intersection of sets. 

set.item 
string set.item(string str, integer index [, string separator= 
Returns the element of the string set at the specified position, including empty elements. 

set.lastIndexOf 
integer set.lastIndexOf(string str, string item [, string separator= 
Returns the position of the last 

set.lastIndexOfExp 
integer set.lastIndexOfExp(string str, string item [, string separator= 
Returns the position of the last 

set.lcsDifference 
integer set.lcsDifference(string set1, string set2 [, string separator= 
Returns the cardinality of the difference of sets ( 

set.lcsDifferenceExp 
integer set.lcsDifferenceExp(string set1, string set2 [, string separator= 
Returns the cardinality of the difference of sets ( 

set.lcsDifferenceResult 
string set.lcsDifferenceResult(string set1, string set2 [, string separator= 
Returns the difference of sets ( 

set.lcsDifferenceResultExp 
string set.lcsDifferenceResultExp(string set1, string set2 [, string separator= 
Returns the difference of sets ( 

set.lcsIntersection 
integer set.lcsIntersection(string set1, string set2 [, string separator= 
Returns the cardinality of the intersection of sets (lcsbased intersection). 

set.lcsIntersectionExp 
integer set.lcsIntersectionExp(string set1, string set2 [, string separator= 
Returns the cardinality of the intersection of sets (lcsbased intersection). 

set.lcsIntersectionResult 
string set.lcsIntersectionResult(string set1, string set2 [, string separator= 
Returns the intersection of sets (lcsbased intersection). 

set.lcsIntersectionResultExp 
string set.lcsIntersectionResultExp(string set1, string set2 [, string separator= 
Returns the intersection of sets (lcsbased intersection). 

set.lcsSymmetricDifference 
integer set.lcsSymmetricDifference(string set1, string set2 [, string separator= 
Returns the cardinality of the symmetric difference of sets (lcsbased intersection). 

set.lcsSymmetricDifferenceExp 
integer set.lcsSymmetricDifferenceExp(string set1, string set2 [, string separator= 
Returns the cardinality of the symmetric difference of sets (lcsbased intersection). 

set.lcsSymmetricDifferenceResult 
string set.lcsSymmetricDifferenceResult(string set1, string set2 [, string separator= 
Returns the symmetric difference of sets (lcsbased intersection). 

set.lcsSymmetricDifferenceResultExp 
string set.lcsSymmetricDifferenceResultExp(string set1, string set2 [, string separator= 
Returns the symmetric difference of sets (lcsbased intersection). 

set.mapExp 
string set.mapExp(set [, string separator= 
Returns a string set whose each element is transformed by the functional parameter.
For example, 

set.size 
integer set.size(string str [, string separator= 
Returns the number of elements in the string, including empty elements. 

set.sort 
string set.sort(string str [, string separator= 
Returns a sorted set of elements. 

set.subSequence 
string set.subSequence(string str, integer from [, integer count= 
Returns elements of the string set starting at the specified position, including empty elements. The 

set.sumExp 
numeric set.sumExp(set [, string separator= 
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, 

set.symmetricDifference 
integer [set.]symmetricDifference(string set1, string set2 [, string separator= 
Returns the cardinality of the symmetric difference of sets. 

set.symmetricDifferenceExp 
integer set.symmetricDifferenceExp(string set1, string set2 [, separator= 
Returns the cardinality of the symmetric difference of sets. 

set.symmetricDifferenceResult 
string set.symmetricDifferenceResult(string set1, string set2 [, string separator= 
Returns the symmetric difference of sets. 

set.symmetricDifferenceResultExp 
string set.symmetricDifferenceResultExp(string set1, string set2 [, string separator= 
Returns the symmetric difference of sets. Example:


set.union 
integer [set.]union(string set1, string set2 [, string separator= 
Returns the cardinality of the union of sets. 

set.unionExp 
integer set.unionExp(string set1, string set2 [, string separator= 
Returns the cardinality of the union of sets. 

set.unionResult 
string set.unionResult(string set1, string set2 [, string separator= 
Returns the union of sets. 

set.unionResultExp 
string set.unionResultExp(string set1, string set2 [, string separator= 
Returns the union of sets. 
Regular expressions
The syntax for regular expressions in ONE follows the rules for regular expressions used in Java, described in Class Pattern documentation.
The following sections contain information about regular expression usage extensions in ONE.
' @" '  syntax (single escaping)
When writing regular expressions, take into consideration that a regular expression is manipulated as a Java string.
In literal Java strings, the backslash (\
) is an escape character.
The literal string \\
is a single backslash.
In regular expressions, the backslash is also an escape character.
The regular expression \\
matches a single backslash.
As a Java string, this regular expression becomes \\\\
.
To avoid having to use double escaping, prefix the string in quotes with an at sign (@
).
In that case, the string between the at sign and double quotation marks (@"some_string"
) is taken as a literal and no characters are considered escape characters in the context of the Java string.
For example, to substitute all occurrences of the caret character (^
) and the closing bracket (]
) with x
in the string ab[^]
(the expected result is the string ab[xx
), you could use the following expression:
substituteAll("[\\^\\]]","x","ab[^]")
Or, using the ' @" ' syntax:
substituteAll(@"[\^\]]","x","ab[^]")
Capturing groups
Matching regular expressions in the input is done by analyzing the input expression string (the string resulting from applying the expression to the input). Sections of the input string (called capturing groups, enclosed in parentheses) are identified and marked for further use in creating the output. These capturing groups can be referenced using backreference.
In the case of a match, the matched data from the input is sent to the predefined output columns. Each output column has a substitution property that is the value which will be sent to the output. It can contain the previously mentioned backreferences with the following syntax:

$i
, wherei
is a number between0
and9`
: Backreference to a capturing group with group number lower than 10. 
${i}
, wherei
is a natural number other than0
: Backreference to a capturing group with any natural group number. 
$`
: Returns the substring before the processed (matched) part of the input string. 
$'
: Returns the substring after the processed (matched) part of the input string. 
$&
: Returns the processed (matched) part of the input string. 
$$
: returns the dollar sign characters ($
).
The capturing groups can be used in expressions substituteAll
, substitutaMany
, and in the Regex Matching step.
For example, to substitute all pairs of letterdigit couples with only the digit from the couple (that is, the input string a1b2c3d4e5
leads to the output 12345
), you could use the following expression:
substituteAll("([az])([09])","${2}","a1b2c3d4e5")
Was this page useful?