Query functions can be used to perform any desired computation on fields of the FIELDS_ADD and FILTER_OUT statements.
numeric_expr1 & numeric_expr2
BITWISE_AND(numeric_expr1, numeric_expr2)
Bitwise AND between numeric expressions.
BITWISE_AND(ipaddr, numeric_expr)
Bitwise AND between IPADDR and numeric arguments.
output type
LONG or IPADDR depending on input argument types.
Example:
FIELDS_ADD(and_op:(ip & 0xffff), and_fn:BITWISE_AND(i, 0xffff));
numeric_expr1 | numeric_expr2
BITWISE_OR(numeric_expr1, numeric_expr2)
Bitwise OR between numeric expressions.
output type
LONG
Example:
FIELDS_ADD(or_op:(i | 1), or_fn:BITWISE_OR(d, 0xffff));
numeric_expr1 ^ numeric_expr2
BITWISE_XOR(numeric_expr1, numeric_expr2)
Bitwise XOR (exclusive or) between numeric expressions.
output type
LONG
Example:
FIELDS_ADD(xor_op:(i ^ 1), xor_fn:BITWISE_XOR(d, 0xffff));
numeric_expr1 << numeric_expr2
LEFT_SHIFT(numeric_expr1, numeric_expr2)
Bitwise shift left numeric_expr1 by number of bits numeric_expr2.
output type
LONG
Example:
FIELDS_ADD(lshift_op:(i << 1), lshift_fn:LEFT_SHIFT(d, 2));
numeric_expr1 >> numeric_expr2
RIGHT_SHIFT(numeric_expr1, numeric_expr2)
Bitwise shift right numeric_expr1 by number of bits numeric_expr2.
output type
LONG
Example:
FIELDS_ADD(rshift_op:i >> 1, rshift_fn:RIGHT_SHIFT(d, 2));
numeric_expr1 >>> numeric_expr2
ZERO_FILL_RIGHT_SHIFT(numeric_expr1, numeric_expr2)
Unsigned bitwise shift right numeric_expr1 by the number of bits of numeric_expr2 (shifts zero into leftmost position).
output type
LONG
Example:
FIELDS_ADD(rshift_op:i >>> 1, rshift_fn:ZERO_FILL_RIGHT_SHIFT(d, 2));
~ numeric_expr
BITWISE_NOT(numeric_expr)
Inverts the bits of numeric_expr.
output type
LONG
Example:
FIELDS_ADD(not_op:~i, not_fn:BITWISE_NOT(i));
BIT_COUNT(numeric_expr)
Returns count of bits set to 1 of numeric_expr.
output type
LONG
Example:
FIELDS_ADD(BIT_COUNT(1));
boolean_expr1 AND boolean_expr2
Logical conjunction between operands boolean_expr1, boolean_expr2. Returns true only when both operands evaluates to true.
Returns NULL if right side operand or both operands evaluate to NULL.
output_type
BOOLEAN
Truth table:
Example:
FIELDS_ADD(false AND true);
boolean_expr1 OR boolean_expr2
Logical disjunction between operands boolean_expr1, boolean_expr2. Returns true only when either of operands evaluate to true.
Returns NULL if right side operand or both operands evaluate to NULL.
output_type
BOOLEAN
Truth table:
Example:
FIELDS_ADD(false OR true);
boolean_expr1 XOR boolean_expr2
Logical exclusive disjunction between operands boolean_expr1, boolean_expr2.
Returns NULL either of the operands evaluate to NULL.
output_type
BOOLEAN
Truth table:
Example:
FIELDS_ADD(true XOR true);
NOT boolean_expr
Returns negation of boolean_expr. Returns NULL if boolean_expr evaluates to NULL.
output_type
BOOLEAN
Truth table:
Example:
FIELDS_ADD(NOT true);
BOOLEAN(string)
Returns TRUE on following string values:
T, t, Y, y, 1, YES, yes, TRUE, true.
Returns NULL if string argument evaluates to NULL.
Returns FALSE on any other string value.
output type
BOOLEAN
Example:
FIELDS_ADD(bval:BOOLEAN('T'));
BOOLEAN(numeric_expr)
numeric_expr value 0 is converted to false, other values to true.
Returns NULL if numeric_expr evaluates to NULL.
output type
BOOLEAN
Example:
FIELDS_ADD(bval:BOOLEAN(i));
BYTES(string)
Converts string argument to BYTES. Returns NULL if string evaluates to NULL.
output type
BYTES
Example:
FIELDS_ADD(b:BYTES('01'));
BYTES(numeric_array)
Converts numeric_array values to BYTES. Returns NULL if numeric_array evaluates to NULL.
output type
BYTES
Example:
FIELDS_ADD(b:BYTES([1023,1024,1]));
DOUBLE(numeric_expr)
Converts numeric_expr to DOUBLE. Returns NULL if numeric_expr evaluates to NULL.
output type
DOUBLE
Example
FIELDS_ADD(d:DOUBLE(2 * 300));
DOUBLE(string)
Converts string argument to DOUBLE. Returns NULL if string evaluates to NULL.
output type
DOUBLE
Example
FIELDS_ADD(d:DOUBLE("-4"));
DURATION(nanoseconds)
Converts numeric nanoseconds value to DURATION. Returns NULL if nanoseconds evaluates to NULL.
output type
DURATION
Example
FIELDS_ADD(DURATION(1000*60*60*24*30*1000000));
INTEGER, INT(numeric_expr)
Converts numeric_expr to INTEGER. Returns NULL if numeric_expr evaluates to NULL.
output type
INTEGER
Example
FIELDS_ADD(i:INT(2.498));
INTEGER, INT(string)
Converts string argument to INTEGER. Returns NULL if string evaluates to NULL.
output type
INTEGER
Example
FIELDS_ADD(i:INT("-4"));
INTEGER, INT(timestamp)
Converts timestamp argument to INTEGER. Returns NULL if timestamp evaluates to NULL.
output type
INTEGER
Example
FIELDS_ADD(i:INT(TIMESTAMP("2019-03-14 22:41:55.000 +0000")));
IPADDR(numeric_expr), IPV4(numeric_expr)
Converts numeric_expr arg to IPADDR. Returns NULL if numeric_expr evaluates to NULL.
output type
IPADDR
Example
FIELDS_ADD(ip:IPADDR(-1062731520));
IPADDR(hi_64_bit_num, low_64_bit_num), IPV6(IPADDR(hi_64_bit_num, low_64_bit_num))
Converts 128-bit numeric argument to IPADDR. Returns NULL if arguments evaluate to NULL.
output type
IPADDR
Example
FIELDS_ADD(ip:IPADDR(1,1));
IPADDR(string), IPV4(string), IPV6(string)
Converts string arg to IPADDR. Returns NULL if string evaluates to NULL.
output type
IPADDR
Example
FIELDS_ADD(ip:IPADDR("192.168.1.0"));
LONG(numeric_expr)
Converts numeric_expr to LONG. Returns NULL if numeric_expr evaluates to NULL.
output type
LONG
Example
FIELDS_ADD(l:LONG(2.498));
LONG(string)
Converts string argument to LONG. Returns NULL if string evaluates to NULL.
output type
LONG
Example
FIELDS_ADD(l:LONG("-4"));
STRING(expr)
Converts expr returning any type to STRING. Returns NULL if expr evaluates to NULL.
output type
STRING
Example
FIELDS_ADD(int_str:STRING(i), double_str:STRING(d), ip_str:STRING(ip));
TIMESTAMP(integer), T(integer)
Converts integer seconds elapsed from Unix epoch to TIMESTAMP.
Returns NULL if integer evaluates to NULL.
output type
TIMESTAMP
Example
FIELDS_ADD(TIMESTAMP(1552603315));
TIMESTAMP(long), T(long)
Converts long milliseconds elapsed from Unix epoch to TIMESTAMP.
Returns NULL if long evaluates to NULL.
output type
TIMESTAMP
Example
FIELDS_ADD(T(1552603315l*1000+333));
TIMESTAMP(year, month, day), T(year, month, day)
Converts date specified by integer arguments to TIMESTAMP. Returns NULL if arguments evaluate to NULL.
output type
TIMESTAMP
Example
FIELDS_ADD(TIMESTAMP(2019,03,14));
TIMESTAMP(year, month, day, hour), T(year, month, day, hour)
Converts date and time specified by integer arguments to TIMESTAMP. Returns NULL if arguments evaluate to NULL.
output type
TIMESTAMP
Example
FIELDS_ADD(TIMESTAMP(2019,03,14,22));
TIMESTAMP(year, month, day, hour, minutes), T(year, month, day, hour, minutes)
Converts date and time specified by integer arguments to TIMESTAMP. Returns NULL if arguments evaluate to NULL.
output type
TIMESTAMP
Example
FIELDS_ADD(TIMESTAMP(2019,03,14,22,41));
TIMESTAMP(year, month, day, hour, minutes, seconds), T(year, month, day, hour, minutes, seconds)
Converts date and time specified by integer arguments to TIMESTAMP. Returns NULL if arguments evaluate to NULL.
output type
TIMESTAMP
Example
FIELDS_ADD(TIMESTAMP(2019,03,14,22,41,55));
TIMESTAMP(year, month, day, hour, minutes, seconds, milliseconds)
T(year, month, day, hour, minutes, seconds, milliseconds)
Converts date and time specified by integer arguments to TIMESTAMP. Returns NULL if arguments evaluate to NULL.
output type
TIMESTAMP
Example
FIELDS_ADD(TIMESTAMP(2019,03,14,22,41,55,333));
TIMESTAMP(string), T(string)
Converts following format string args to TIMESTAMP:
Returns NULL if argument string evaluates to NULL.
output type
TIMESTAMP
Example
FIELDS_ADD(TIMESTAMP("2019-03-14 22:41:55.333 GMT"));
TO_NULL(expr)
Returns NULL value.
output type
the type of supplied argument
Example
FIELDS_ADD(n_i:TO_NULL(i), n_ip:TO_NULL(ip), n_time:TO_NULL(t), n_str:TO_NULL(s));
Result details (double click on resultset):
TUPLE(expr, …)
Converts one or more expressions returning any data type to TUPLE.
output type
TUPLE
Example
FIELDS_ADD(t:TUPLE(1,2.0,"foo")) | FIELDS_ADD(TYPE(t));
VARIANT(expr)
VAR(expr)
Converts primitive type expr to VARIANT. Returns NULL if expr evaluates to NULL.
output type
VARIANT
Example
FIELDS_ADD(var:VARIANT(s));
Result details (double click on resultset row):
VARIANT_ARRAY(arg)
VAR_ARR(arg)
Converts ARRAY or primitive type arg to VARIANT_array
output type
VARIANT_array
Example
FIELDS_ADD(res:VARIANT_ARRAY([1,2,3])) | FIELDS_ADD(TYPE(res));
VARIANT_OBJECT(expr)
Converts TUPLE, VARIANT or primitive type expr to VARIANT_OBJECT.
output type
VARIANT_OBJECT
Example
FIELDS_ADD(vo_tuple:VARIANT_OBJECT({a:1, b:2})) | FIELDS_ADD(TYPE(vo_tuple));
EMPTY_ARRAY(type)
Creates an empty array of type
NULL_ARRAY(type)
Creates a null array of type
output_type
ARRAY
NULL_TUPLE(type)
Creates a null tuple of type
output_type
TUPLE
expr1 = expr2
EQUAL(expr1, expr2)
Returns true if the expressions are equal.
Returns NULL if expr1 or expr2 arguments evaluate to NULL.
Returns FALSE otherwise.
output type
BOOLEAN
Example:
FIELDS_ADD(1 = 1);
expr1 <> expr2
expr1 != expr2
NOT_EQUAL(expr1, expr2)
Returns true if the expressions are not equal.
Returns NULL if expr1 or expr2 arguments evaluate to NULL.
Returns FALSE otherwise.
output type
BOOLEAN
Example:
FIELDS_ADD(1 != 1);
expr1 > expr2
GREATER(expr1, expr2)
Returns true if expr1 is greater than expr2.
Returns NULL if expr1 or expr2 arguments evaluate to NULL.
Returns FALSE otherwise.
output type
BOOLEAN
Example:
FIELDS_ADD(gr_op:2 > 1, gr_f:GREATER(1,1));
expr1 < expr2
LESS(expr1, expr2)
Returns true if expr1 is less than expr2.
Returns NULL if expr1 or expr2 arguments evaluate to NULL.
Returns FALSE otherwise.
output type
BOOLEAN
Example:
FIELDS_ADD(l_op:1 < 2, l_f:LESS(1,1));
expr1 >= expr2
GREATER_OR_EQUAL(expr1, expr2)
Returns true if expr1 is greater than or equal to expr2.
Returns NULL if expr1 or expr2 arguments evaluate to NULL.
Returns FALSE otherwise.
output type
BOOLEAN
Example:
FIELDS_ADD(gr_op:2 >= 1, gr_f:GREATER_OR_EQUAL(1,1));
expr1 <= expr2
LESS_OR_EQUAL(expr1, expr2)
Returns true if expr1 is less than or equal to expr2.
Returns NULL if expr1 or expr2 arguments evaluate to NULL.
Returns FALSE otherwise.
output type
BOOLEAN
Example:
FIELDS_ADD(gr_op:1 <= 2, gr_f:GREATER_OR_EQUAL(1,1));
expr IS NULL
IS_NULL(expr)
Returns true if expr is NULL, otherwise returns false.
output type
BOOLEAN
Example:
FIELDS_ADD(s, is_null:IS_NULL(s));
expr IS NOT NULL
IS_NOT_NULL(expr)
Returns true if expr is NOT NULL, otherwise returns false.
output type
BOOLEAN
Example:
FIELDS_ADD(s, is_not_null:s IS NOT NULL);
expr IN array
Returns true if expr is present in array, otherwise returns FALSE.
expr NOT IN array
A negated version of the above: Returns true if expr is not present in array, otherwise returns FALSE.
Returns NULL if array evaluates to NULL.
output type
BOOLEAN
Note
the expr has to be the same type as items in the array.
Example:
FIELDS_ADD(str_in:'a' IN ['b','c','a']);
expr IN list
Returns true if expr is present in list, otherwise returns FALSE.
expr NOT IN list
A negated version of the above: Returns true if expr is not present in list, otherwise returns FALSE.
Returns NULL if list evaluates to NULL.
output type
BOOLEAN
Note
All members of the list have to be the same type as the expr being tested…
FALSE_OR_NULL(boolean_expr)
Returns true if boolean_expr returns false or null
output type
BOOLEAN
Example:
FIELDS_ADD(i, FALSE_OR_NULL(i = 0));
TRUE_OR_NULL(boolean_expr)
Returns true if boolean_expr returns true or null
output type
BOOLEAN
Example:
FIELDS_ADD(i, TRUE_OR_NULL(i = 0));
VARIANT_FIELD_SELECT(variant, fieldname)
Selects fieldname from VARIANT_OBJECT or VARIANT_ARRAY variant object. This function can be used when arguments are evaluated dynamically at runtime.
Note
The function is complementary to accessing object elements directly.
output type
the type of the fieldname selected.
Example: select member named "ip" from a variant object:
FIELDS_ADD(obj:VARIANT_OBJECT(tuple)) | FIELDS_ADD(VARIANT_FIELD_SELECT(obj, 'ip'))
TUPLE_FIELD_SELECT(tuple, fieldname)
Selects fieldname from tuple. This function can be used when arguments are evaluated dynamically at runtime.
Note
The function is complementary to accessing tuple elements directly.
output type
the type of the fieldname selected
Example: select member named "ip" from a variant object:
FIELDS_ADD(tuple) | FIELDS_ADD(TUPLE_FIELD_SELECT(tuple, 'ip'))
ARRAY_AVG(array)
Computes arithmetic mean of numeric array members.
output type
DOUBLE
Example:
FIELDS_ADD(array:[0,1,2,3,4]) | FIELDS_ADD(ARRAY_AVG(array));
ARRAY_COUNT(array)
Computes count of not NULL members of array.
output type
LONG
Example:
FIELDS_ADD(array:[0,1,2,3,4]) | FIELDS_ADD(ARRAY_COUNT(array));
ARRAY_FIRST(array)
Returns the first value in the array.
output type
the type of the array member
Example:
FIELDS_ADD(array:[0,1,2,3,4]) | FIELDS_ADD(ARRAY_FIRST(array));
ARRAY_LAST(array)
Returns the last value in the array.
output type
the type of the array member
Example:
FIELDS_ADD(array:[0,1,2,3,4]) | FIELDS_ADD(ARRAY_LAST(array));
ARRAY_MAX(array)
Returns the max value of numeric array members.
output type
the type of the array member
Example:
FIELDS_ADD(array:[0,1,2,3,4]) | FIELDS_ADD(ARRAY_MAX(array));
ARRAY_MIN(array) Returns min value of numeric array members.
output type
the type of the array member
Example:
FIELDS_ADD(array:[0,1,2,3,4]) | FIELDS_ADD(ARRAY_MIN(array));
ARRAY_JOIN(array, str)
Returns string with array elements concatenated in order of their appearance and separated by str.
output type
STRING
Example:
FIELDS_ADD(array:[0,1,2,3,4]) | FIELDS_ADD(ARRAY_JOIN(array, " + "));
ARRAY_SORT(array)
Returns copy of array with members sorted in ascending order.
output type
ARRAY
Example:
FIELDS_ADD(array:[2,1,0,4,3]) | FIELDS_ADD(ARRAY_SORT(array));
ARRAY_SORT(array, boolean)
Returns sorted array. Sorting order is determined by boolean argument: if true then ascending, otherwise descending.
output type
ARRAY
Example:
FIELDS_ADD(array:[2,1,0,4,3]) | FIELDS_ADD(ARRAY_SORT(array, false));
ARRAY_SUM(array)
Computes the sum of numeric array members.
output type
DOUBLE
Example:
FIELDS_ADD(array:[0,1,2,3,4]) | FIELDS_ADD(ARRAY_SUM(array));
ARRAY_IDX(array, expr)
Returns position of the first member in array which is equal to expr. If not found returns NULL.
output type
the type of array member
Example:
FIELDS_ADD(array:[0,1,2,3,4]) | FIELDS_ADD(ARRAY_IDX(array,4));
ARRAY_SELECT(array, integer)
Returns element from array at position integer. If not found returns NULL.
output type
the type of array member.
Example:
FIELDS_ADD(array:[0,1,2,3,4]) | FIELDS_ADD(ARRAY_SELECT(array,4));
ARRAY_SELECT(array, start_pos, end_pos)
Returns elements of array from start_pos to end_pos inclusive. If not found returns empty array.
output type
ARRAY
Example:
FIELDS_ADD(array:[0,1,2,3,4]) | FIELDS_ADD(ARRAY_SELECT(array,1,3));
ARRAY_LEN(array)
Returns the number of elements in array.
output type
INTEGER
Example:
FIELDS_ADD(array:[0,1,2,3,4]) | FIELDS_ADD(ARRAY_LEN(array));
ARRAY_REMOVE_NULLS(array)
Removes elements with NULL values from array.
output type
ARRAY
Example:
FIELDS_ADD(array:[0,1,TO_NULL(2),3,4])| FIELDS_ADD(array, new_array:ARRAY_REMOVE_NULLS(array));
ARRAY_REVERSE(array)
Returns array with elements in reversed order.
output type
ARRAY
Example:
FIELDS_ADD(arr:[0,1,2,3]) | FIELDS_ADD(arr, reverse_arr:ARRAY_REVERSE(arr))
REMOVE_NULLS(variant_object)
REMOVE_NULLS(variant_array)
REMOVE_NULLS(array)
Removes elements with NULL values from variant_object, variant_array or array argument.
output type
VARIANT_OBJECT or VARIANT_ARRAY or ARRAY
Example:
FIELDS_ADD(v_obj:VARIANT_OBJECT({a:1, b:"oho", c:TO_NULL(1)}))| FIELDS_ADD(v_obj, REMOVE_NULLS(v_obj));
ARRAY_UNIQ(array)
Returns array with unique values from argument array.
output type
ARRAY
Example:
FIELDS_ADD(arr:[1,2,3,4,5,1,1,2]) | FIELDS_ADD(arr, ARRAY_UNIQ(arr));
SPREAD(tuple)
Expands fields from the TUPLE argument to the fields of resultset row.
Example:
FIELDS_ADD(tuple, SPREAD(tuple));
MD5(string_expr)
Computes MD5 hash value of string_expr.
output type
STRING
Example:
FIELDS_ADD(MD5('Dynatrace'));
SHA1(string_expr)
Computes SHA1 hash value of string.
output type
STRING
Example:
FIELDS_ADD(SHA1('Dynatrace'));
timestamp_value[+ amount time_unit]
Adds amount of time_units to timestamp_value.
Time units:
ms or millis, sec or s, min or m, hour or h, day or d, week or w
output_type
TIMESTAMP
Example:
FIELDS_ADD(now(), now_add_1_min:now()[+1 min]);
timestamp_value[- amount time_unit]
Subtracts amount of time_units from timestamp_value.
Time units: ms or millis, sec or s, min or m, hour or h, day or d, week or w
output_type
TIMESTAMP
Example:
FIELDS_ADD(now(), time_subtract_1_sec:now()[-1 sec]);
timestamp_value[amount time_unit]
timestamp_value[% amount time_unit]
Truncates the timestamp_value to amount of time_units.
Time units: ms or millis, sec or s, min or m, hour or h, day or d, week or w
output_type
TIMESTAMP
Example:
FIELDS_ADD(now(), now_truncated_1_h:now()[1 hour]);
DAY(timestamp_expr)
Returns an integer representing the day of the month from timestamp_expr.
output_type
INTEGER
Example:
FIELDS_ADD(DAY(T('2017-01-22 15:00:00')));
DAY_OF_WEEK(timestamp_expr)
Returns an integer representing 1 - 7 day of the week from timestamp_expr.
output_type
INTEGER
Example:
FIELDS_ADD(DAY_OF_WEEK(T('2017-01-22 15:00:00')));
DAY_OF_YEAR(timestamp_expr)
Returns integer representing 1 - 365 day of the year (or 366 in the leap year) from timestamp_expr
output_type
INTEGER
Example:
FIELDS_ADD(DAY_OF_YEAR(T('2017-05-22 15:00:00')));
HOUR(timestamp_expr)
Returns an integer representing 0 - 23 hour of the day from timestamp_expr
output_type
INTEGER
Example:
FIELDS_ADD(HOUR(T('2016-09-23 17:58:00')));
MINUTE(timestamp_expr)
Returns an integer representing 0 - 59 minute of the hour from timestamp_expr.
output_type
INTEGER
Example:
FIELDS_ADD(MINUTE(T('2016-09-23 17:58:00')));
MONTH(timestamp_expr)
Returns an integer representing 1 - 12 month of the year from timestamp_expr.
output_type
INTEGER
Example:
FIELDS_ADD(MONTH(T('2016-09-23 17:58:00')));
NOW()
Returns the query execution start timestamp.
output_type
TIMESTAMP
Example:
FIELDS_ADD(t, NOW());
SECOND(timestamp_expr)
Returns integer representing 0 - 59 second of the minute from timestamp_expr.
output_type
INTEGER
Example:
FIELDS_ADD(SECOND(T('2016-09-23 17:58:32')));
STR_SEC_TO_TIME(seconds_str)
Converts seconds_str seconds, elapsed from Unix epoch to TIMESTAMP.
output_type
TIMESTAMP
Example:
FIELDS_ADD(STR_SEC_TO_TIME("1552603315"));
STR_TO_TIME(string)
Converts string to TIMESTAMP. Argument string must be in one of the:
output_type
TIMESTAMP
Example:
FIELDS_ADD(STR_TO_TIME('2016-09-23 17:58:00'));
SYS_TIME()
Returns the current timestamp from the operating system clock.
output_type
TIMESTAMP
Example:
FIELDS_ADD(SYS_TIME());
TIME_ADD(timestamp, millis)
Adds millis of milliseconds to timestamp.
output_type
TIMESTAMP
Example:
FIELDS_ADD(t, TIME_ADD(t, 1000));
TIME_ADD_DAY(timestamp, numeric_expr)
Adds numeric_expr of days to timestamp and returns resulting TIMESTAMP value.
output_type
TIMESTAMP
Example:
FIELDS_ADD(now:NOW(), a_year_from_now:TIME_ADD_DAY(NOW(), 365));
TIME_ADD_WEEK(timestamp, numeric_expr)
Adds numeric_expr of weeks to timestamp and returns resulting TIMESTAMP value.
output_type
TIMESTAMP
Example:
FIELDS_ADD(now:NOW(), about_a_year_from_now:TIME_ADD_WEEK(NOW(), 52))
TIME_ADD_MONTH(timestamp, numeric_expr)
Adds numeric_expr of months to timestamp and returns resulting TIMESTAMP value.
output_type
TIMESTAMP
Example:
FIELDS_ADD(now:NOW(), about_a_year_from_now:TIME_ADD_MONTH(NOW(), 12))
TIME_ADD_YEAR(timestamp, numeric_expr)
Adds numeric_expr of months to timestamp and returns resulting TIMESTAMP value.
output_type
TIMESTAMP
Example:
FIELDS_ADD(now:NOW(), a_century_from_now:TIME_ADD_YEAR(NOW(), 100))
TIME_SUB(timestamp, millis)
Subtracts millis milliseconds from timestamp.
output_type
TIMESTAMP
Example:
FIELDS_ADD(t, TIME_SUB(t, 60*1000));
TIME_SUB_DAY(timestamp, numeric_expr)
Subtracts numeric_expr days from timestamp and returns resulting TIMESTAMP value.
output_type
TIMESTAMP
Example:
FIELDS_ADD(now:NOW(), a_day_ago:TIME_SUB_DAY(NOW(), 1));
TIME_SUB_DAY(timestamp, numeric_expr)
Subtracts numeric_expr weeks from timestamp and returns resulting TIMESTAMP value.
output_type
TIMESTAMP
Example:
FIELDS_ADD(now:NOW(), a_week_ago:TIME_SUB_WEEK(NOW(), 1));
TIME_SUB_MONTH(timestamp, numeric_expr)
Subtracts numeric_expr months from timestamp and returns resulting TIMESTAMP value.
output_type
TIMESTAMP
Example:
FIELDS_ADD(now:NOW(), a_month_ago:TIME_SUB_MONTH(NOW(), 1));
TIME_SUB_YEAR(timestamp, numeric_expr)
Subtracts numeric_expr years from timestamp and returns resulting TIMESTAMP value.
output_type
TIMESTAMP
Example:
FIELDS_ADD(now:NOW(), a_century_ago:TIME_SUB_YEAR(NOW(), 100));
TIME_SUBMOD(timestamp, millis)
Returns timestamp modulo millis - i.e truncates timestamp to the precision of millis milliseconds.
output_type
TIMESTAMP
Example Truncate timestamp to the precision of hour:
FIELDS_ADD(TIME_SUBMOD(T('2016-09-23 17:58:00'), 60*60*1000));
TIME_SUBMOD_DAY(timestamp, numeric_expr)
Returns timestamp truncated to the precision of numeric_expr days.
output_type
TIMESTAMP
Example:
FIELDS_ADD(now:NOW(), trunc_day:TIME_SUBMOD_DAY(NOW(), 1));
TIME_SUBMOD_WEEK(timestamp, numeric_expr)
Returns timestamp truncated to the precision of numeric_expr weeks.
output_type
TIMESTAMP
Example:
FIELDS_ADD(now:NOW(), trunc_4_week:TIME_SUBMOD_WEEK(NOW(), 4));
TIME_SUBMOD_WEEK(timestamp, numeric_expr)
Returns timestamp truncated to the precision of numeric_expr months.
output_type
TIMESTAMP
Example:
FIELDS_ADD(now:NOW(), trunc_1_month:TIME_SUBMOD_MONTH(NOW(), 1));
TIME_SUBMOD_YEAR(timestamp, numeric_expr)
Returns timestamp truncated to the precision of numeric_expr years.
output_type
TIMESTAMP
Example:
FIELDS_ADD(now:NOW(), trunc_1_year:TIME_SUBMOD_YEAR(NOW(), 1));
TIME_TO_STR(timestamp, format_str)
Converts timestamp to string as specified by format_str <timestamp_format_str>.
output_type
STRING
Example:
FIELDS_ADD(TIME_TO_STR(t, "EEEE, MMMM d 'anno domini' yyyy") );
TO_STR_SEC(timestamp)
Converts timestamp to string of floating-point seconds elapsed from Unix epoch (the digits in the fraction part represent nanoseconds).
output_type
STRING
Example:
FIELDS_ADD(t, TO_STR_SEC(t));
YEAR(timestamp_expr)
Returns an integer representing year from timestamp_expr.
output_type
INTEGER
Example:
FIELDS_ADD(YEAR(T('2017-01-22 15:00:00')));
WEEK_OF_YEAR(timestamp_expr)
Returns an integer representing the week of the year of timestamp_expr.
output_type
INTEGER
Example:
FIELDS_ADD(WEEK_OF_YEAR(T('2017-01-22 15:00:00')));
IF(boolean_expr,true_expr,[else_expr])
IF_THEN(boolean_expr,true_expr,[else_expr])
IF_THEN_ELSE(boolean_expr,true_expr,[else_expr])
Returns true_expr if boolean_expr evaluates to true, otherwise returns else_expr.
output type
the type returned by true_expr or else_expr (must be the same).
Example:
FIELDS_ADD(s, IF(s CONTAINS '0', 'contains zero', 'has no zero'));
IF_THEN(boolean_expr,true_expr)
IF_THEN_ELSE(boolean_expr,true_expr)
Returns true_expr if boolean_expr evaluates to true, otherwise returns NULL.
output type
the type returned by true_expr or else_expr (must be the same).
Example:
FIELDS_ADD(s, IF(s CONTAINS '0', 'contains zero'));
numeric_expr1 + numeric_expr2
Arithmetic addition of numeric_expr2 to numeric_expr1.
output type
LONG when either of numeric_expr is INTEGER or LONG
DOUBLE
IPADDRwhen either of numeric_expr isIPADDR
TIMESTAMPwhen one of numeric_expr isTIMESTAMP(see also timestamp expressions)
Example:
FIELDS_ADD(i, int_add:i+1,d, double_add:d+1,ip, ipaddr_add:ip + 1,t, time_add: t+1000);
numeric_expr1 - numeric_expr2
Arithmetic subtraction of numeric_expr2 from numeric_expr1.
output type
LONG when either of numeric_expr is INTEGER or LONG
DOUBLEwhen either of numeric_expr isDOUBLE
IPADDRwhen either of numeric_expr isIPADDR
TIMESTAMPwhen one of numeric_expr isTIMESTAMP(see also timestamp expressions)
Example:
FIELDS_ADD(i, int_sub:i-1,d, double_sub:d-1,ip, ipaddr_sub:ip - 1,t, time_sub: t-1000);
numeric_expr1 * numeric_expr2
Arithmetic multiplication of numeric_expr1 by numeric_expr2.
output type
LONG when either of numeric_expr is INTEGER or LONG
DOUBLEwhen either of numeric_expr isDOUBLE
Example:
FIELDS_ADD(i, int_mult:i * 2, d, double_mult:d * 2);
numeric_expr1 / numeric_expr2, DIVIDE(numeric_expr1, numeric_expr2)
Arithmetic division of numeric_expr1 by numeric_expr2.
output type
LONG when either of numeric_expr is INTEGER or LONG
DOUBLEwhen either of numeric_expr isDOUBLE
Example:
FIELDS_ADD(i, int_div:i / 2, d, double_div:d / 2);
numeric_expr1 % numeric_expr2, MODULO(numeric_expr1, numeric_expr2)
Computes remainder (modulo) of division numeric_expr1 by numeric_expr2.
output type
LONG when either of numeric_expr is INTEGER or LONG
DOUBLEwhen either of numeric_expr isDOUBLE
Example:
FIELDS_ADD(i, int_mod:i % 2, d, double_mod:d % 2);
ABS(numeric_expr)
Returns absolute value of numeric_expr. Returns NULL if numeric_expr evaluatess to NULL.
output type
DOUBLE
Example:
FIELDS_ADD(ABS(-10));
ADD(numeric_expr1, numeric_expr2)
Arithmetic addition of numeric_expr2 to numeric_expr1. Returns NULL if either of the arguments evaluates to NULL.
output type
LONG when either of numeric_expr is INTEGER or LONG
DOUBLEwhen either of numeric_expr isDOUBLE
IPADDRwhen either of numeric_expr isIPADDR
TIMESTAMPwhen one of numeric_expr isTIMESTAMP(see also timestamp expressions)
Example:
FIELDS_ADD(i, int_add:ADD(i, 1),d, double_add:ADD(d, 1),ip, ipaddr_add:ADD(ip, 1),t, time_add:ADD(t, 1000));
ACOS(numeric_expr)
Computes arc cosine of numeric expr. The returned angle is in the range 0.0 through pi.
Returns NULL if numeric_expr evaluates to NULL.
output type
DOUBLE
Example:
FIELDS_ADD(ACOS(0.5));
ASIN(numeric_expr)
Computes arc sine of numeric_expr. The returned angle is in the range -pi/2 through pi/2.
Returns NULL if numeric_expr evaluates to NULL.
output type
DOUBLE
Example:
FIELDS_ADD(ASIN(0.5));
ATAN(numeric_expr)
Computes arc tangent of numeric_expr. The returned angle is in the range -p/2 through pi/2.
Returns NULL if numeric_expr evaluates to NULL.
output type
DOUBLE
Example:
FIELDS_ADD(ATAN(0.5));
ATAN2(numeric_expr1, numeric_expr2)
Computes the angle theta from the conversion of rectangular coordinates x,y to polar coordinates(r, theta).
Returns NULL if either of the arguments evaluates to NULL.
output type
DOUBLE
Example:
FIELDS_ADD(ATAN2(0.5, 0.5));
CBRT(numeric_expr)
Computes the cube root of numeric_expr.
Returns NULL if numeric_expr evaluates to NULL.
output type
DOUBLE
Example:
FIELDS_ADD(CBRT(27));
CEIL(numeric_expr)
Computes the smallest (closest to negative infinity) double value that is greater than or equal to the numeric_expr and is equal to a mathematical integer.
Returns NULL if numeric_expr evaluates to NULL.
output type
DOUBLE
Example:
FIELDS_ADD(CEIL(0.7));
COS(numeric_expr)
Computes the trigonometric cosine of an angle numeric_expr (in radians).
Returns NULL if numeric_expr evaluates to NULL.
output type
DOUBLE
Example:
FIELDS_ADD(COS(0.5));
COSH(numeric_expr)
Computes the hyperbolic cosine of an angle numeric_expr.
Returns NULL if numeric_expr evaluates to NULL.
output type
DOUBLE
Example:
FIELDS_ADD(COSH(0.5));
DEGREES(numeric_expr)
Converts numeric expr angle (of radians) to an approximately equivalent angle of degrees.
Returns NULL if numeric_expr evaluates to NULL.
output type
DOUBLE
Example:
FIELDS_ADD(DEGREES(1.571));
DIVIDE(numeric_expr1, numeric_expr2)
Arithmetic division of numeric_expr1 by numeric_expr2. Returns NULL if any of the arguments evaluates to NULL.
output type
LONG when either of numeric_expr is INTEGER or LONG
DOUBLEwhen either of numeric_expr isDOUBLE
Example:
FIELDS_ADD(i, int_div:DIVIDE(i, 2), d, double_div:DIVIDE(d, 2));
E()
Returns Euler's number.
output type
DOUBLE
Example:
FIELDS_ADD(E());
EXP(numeric_expr)
Computes Euler's number e raised to the power of numeric_expr.
Returns NULL if numeric_expr evaluates to NULL.
output type
DOUBLE
Example:
FIELDS_ADD(EXP(2));
EXPM1(numeric_expr)
Returns e numeric_expr -1
Returns NULL if numeric_expr evaluates to NULL.
output type
DOUBLE
Example:
FIELDS_ADD(EXPM1(2));
FLOOR(numeric_expr)
Computes the largest (closest to positive infinity) DOUBLE value, less than or equal to the numeric expr and is equal to a mathematical integer.
Returns NULL if numeric_expr evaluates to NULL.
output type
DOUBLE
Example:
FIELDS_ADD(FLOOR(2.71));
FLOORDIV(numeric_expr1, numeric_expr2)
Computes mathematical integer floor of numeric_expr1 argument division by numeric_expr2.
Returns NULL if either of the arguments evaluates to NULL.
output type
INTEGER when both arguments are INTEGER
LONGwhen any of the arguments areLONGorDOUBLE
Example:
FIELDS_ADD(FLOORDIV(5, 2.0));
FLOORMOD(numeric_expr1, numeric_expr2)
Computes mathematical integer floor modulus of numeric_exp1 and numeric_expr2.
Returns NULL if either of the arguments evaluates to NULL.
output type
INTEGER when both arguments are INTEGER
LONGwhen any of the arguments areLONGorDOUBLE
Example:
FIELDS_ADD(FLOORMOD(5, 2.71));
GETEXPONENT(numeric_expr)
Computes mathematical integer unbiased exponent used in the representation of numeric_expr argument.
Returns NULL if numeric_expr evaluates to NULL.
output type
INTEGER
Example:
FIELDS_ADD(GETEXPONENT(28.71d));
HYPOT(numeric_expr1, numeric_expr2)
Returns sqrt(x 2 +y2).
Returns NULL if numeric_expr evaluates to NULL.
output type
DOUBLE
Example:
FIELDS_ADD(HYPOT(3.0, 4))
IEEEREMAINDER(numeric_expr1, numeric_expr2)
Returns DOUBLE value of the remainder operation on numeric arguments as prescribed by the IEEE 754 standard.
Returns NULL if either of the arguments evaluates to NULL.
output type
DOUBLE
Example:
FIELDS_ADD(IEEEREMAINDER(7.389f, 5));
LOG(numeric_expr)
Computes the natural logarithm (base e) of numeric_expr.
Returns NULL if numeric_expr evaluates to NULL.
output type
DOUBLE
Example:
FIELDS_ADD(LOG(7.389));
LOG1P(numeric_expr)
Computes the natural logarithm (base e) of the sum of the numeric_expr and 1.
Returns NULL if numeric_expr evaluates to NULL.
output type
DOUBLE
Example:
FIELDS_ADD(LOG1P(7.389));
LOG10(numeric_expr)
Computes base 10 logarithm of numeric_expr.
Returns NULL if numeric_expr evaluates to NULL.
output type
DOUBLE
Example:
FIELDS_ADD(LOG10(7.389));
MODULO(numeric_expr1, numeric_expr2)
Computes remainder (modulo) of division numeric_expr1 by numeric_expr2.
Returns NULL if either of the arguments evaluates to NULL.
output type
LONG when either of numeric_expr is INTEGER or LONG
DOUBLEwhen either of numeric_expr isDOUBLE
Example:
FIELDS_ADD(i, int_mod:MODULO(i, 2), d, double_mod:MODULO(d, 2));
MULTIPLY(numeric_expr1, numeric_expr2)
Arithmetic multiplication of numeric_expr1 by numeric_expr2.
Returns NULL if either of the arguments evaluates to NULL.
output type
LONG when either of numeric_expr is INTEGER or LONG
DOUBLEwhen either of numeric_expr isDOUBLE
Example:
FIELDS_ADD(i, int_mult:MULTIPLY(i, 2), d, double_mult:MULTIPLY(d, 2));
NEXTAFTER(numeric_expr1, numeric_expr2)
Returns number adjacent to the numeric_expr1 in the direction of the numeric_expr2.
Returns NULL if either of the arguments evaluates to NULL.
output type
DOUBLE
Example:
FIELDS_ADD(NEXTAFTER(3.33f, 2));
NEXTDOWN(numeric_expr)
Returns the number adjacent to numeric_expr argument in the direction of negative infinity.
Returns NULL if numeric_expr evaluates to NULL.
output type
DOUBLE
Example:
FIELDS_ADD(NEXTDOWN(3.33));
NEXTUP(numeric_expr)
Returns the number adjacent to numeric_expr argument in the direction of positive infinity.
Returns NULL if numeric_expr evaluates to NULL.
output type
DOUBLE
Example:
FIELDS_ADD(NEXTUP(3.33));
PI()
Returns the value of pi.
output type
DOUBLE
Example:
FIELDS_ADD(PI());
POWER(numeric_expr1, numeric_expr2)
Computes the value of numeric_expr1 raised to the power of numeric_expr2.
Returns NULL if either of the arguments evaluates to NULL.
output type
DOUBLE
Example:
FIELDS_ADD(POWER(2, 3.5));
RADIANS(numeric_expr)
Converts numeric_expr angle of degrees to an approximately equivalent angle of radians.
Returns NULL if numeric_expr evaluates to NULL.
output type
DOUBLE
Example:
FIELDS_ADD(RADIANS(90));
RANDOM()
Returns random positive double value greater than or equal to 0.0 and less than 1.0.
output type
DOUBLE
Example:
FIELDS_ADD(RANDOM());
RINT(numeric_expr)
Returns the value that is closest in value to the numeric_expr argument and is equal to a mathematical integer.
Returns NULL if numeric_expr evaluates to NULL.
output type
DOUBLE
Example:
FIELDS_ADD(RINT(12.396));
ROUND(numeric_expr)
Computes closest mathematical integer to numeric_expr with ties rounding up.
Returns NULL if numeric_expr evaluates to NULL.
output type
DOUBLE
Example:
FIELDS_ADD(ROUND(12.396));
ROUND(numeric_expr, decimalplaces)
Computes closest mathematical integer to numeric_expr with decimalplaces number of decimals.
Returns NULL if numeric_expr evaluates to NULL.
output type
DOUBLE
Example:
FIELDS_ADD(ROUND(12.436,2));
SCALB(numeric_expr1, scale_factor)
Returns FLOAT f×2scale_factor
Returns NULL if either of the arguments evaluates to NULL.
output type
DOUBLE
Example:
FIELDS_ADD(SCALB(0.524f, 6));
SIGNUM(numeric_expr)
Returns result of signum function of argument:
Returns NULL if numeric_expr evaluates to NULL.
output type
DOUBLE
Example:
FIELDS_ADD(SIGNUM(28.71));
SIN(numeric_expr)
Computes the trigonometric sine of angle numeric_expr (in radians).
Returns NULL if numeric_expr evaluates to NULL.
output type
DOUBLE
Example:
FIELDS_ADD(SIN(0.524));
SINH(numeric_expr)
Computes the hyperbolic sine of numeric_expr.
Returns NULL if numeric_expr evaluates to NULL.
output type
DOUBLE
Example:
FIELDS_ADD(SINH(0.524));
SQRT(numeric_expr)
Computes the positive square root of numeric_expr.
Returns NULL if numeric_expr evaluates to NULL.
output type
DOUBLE
Example:
FIELDS_ADD(SQRT(9.781));
SUBMOD(numeric_expr1, numeric_expr2)
Subtracts numeric_expr1 modulo numeric_expr2 from numeric_expr1.
Returns NULL if either of the arguments evaluates to NULL.
output type
the type of numeric_expr1
Example:
FIELDS_ADD(SUBMOD(9.2, 4));
SUBTRACT(numeric_expr1, numeric_expr2)
Arithmetic subtraction of numeric_expr2 from numeric_expr1.
output type
LONG when either of numeric_expr is INTEGER or LONG
DOUBLEwhen either of numeric_expr isDOUBLE
IPADDRwhen either of numeric_expr isIPADDR
TIMESTAMPwhen one of numeric_expr isTIMESTAMP(see also timestamp expressions)
Example:
FIELDS_ADD(i, int_sub:SUBTRACT(i, 1),d, double_sub:SUBTRACT(d,1),ip, ipaddr_sub:SUBTRACT(ip, 1),t, time_sub:SUBTRACT(t, 1000));
TAN(numeric_expr)
Computes the trigonometric tangent of angle numeric_expr (in radians).
Returns NULL if numeric_expr evaluates to NULL.
output type
DOUBLE
Example:
FIELDS_ADD(TAN(1.524));
TANH(numeric_expr)
Computes the hyperbolic tangent of numeric_expr.
Returns NULL if numeric_expr evaluates to NULL.
output type
DOUBLE
Example:
FIELDS_ADD(TANH(1.524));
TOHEXSTRING (numeric_expr)
Converts INTEGER or LONG numeric_expr to hexadecimal string.
Returns NULL if numeric_expr evaluates to NULL.
output type
STRING
Example:
FIELDS_ADD(TOHEXSTRING(1000));
ULP(numeric_expr)
Returns size of an ulp of the numeric_expr argument.
Returns NULL if numeric_expr evaluates to NULL.
output type
DOUBLE
Example:
FIELDS_ADD(ULP(1.524f));
DSLICE(domain, level)
Returns substring of domain containing labels up to the level specified.
Returns NULL if argument evaluates to NULL.
output type
STRING
Example:
FIELDS_ADD(DSLICE('www.dynatrace.com',2));
IP_TRUNC(ip_addr, prefix_len)
Returns an IPADDR where first prefix_len bits are equal to those in ip_addr and the remaining are set to 0.
Returns NULL if argument evaluates to NULL.
output type
IPADDR
Example: retain first 24-bits of an IPv4 address
FIELDS_ADD(ipv4:IPADDR("192.168.100.100"), ipv6:IPADDR("2a00:1450:4010:c05::69"))| FIELDS_ADD(ipv4_trunc:IP_TRUNC(ipv4, 24), ipv6_trunc:IP_TRUNC(ipv6, 24))
IP_TRUNC(ip_addr, ipv4_prefix_len, ipv6_prefix_len)
Returns an IPADDR where first prefix_len bits are equal to those in ip_addr and the remaining are set to 0. Allows to specify different
prefix lengths for IPv4 and IPv6 type addresses (ipv4_prefix_len and ipv6_prefix_len respectively).
Returns NULL if argument evaluates to NULL.
output type
IPADDR
Example: retain first 24-bits of an IPv4 address
FIELDS_ADD(ipv4:IPADDR("192.168.100.100"), ipv6:IPADDR("2a00:1450:4010:c05::69"))| FIELDS_ADD(ipv4_trunc:IP_TRUNC(ipv4, 24,96), ipv6_trunc:IP_TRUNC(ipv6, 24,96))
IS_IPV4(ip_expr)
Returns true if IPADDR contains IPv4 address, otherwise returns false.
Returns NULL if argument evaluates to NULL.
output type
BOOLEAN
Example:
FIELDS_ADD(IS_IPV4(200.100.32.45:99));
IS_IPV6(ip_expr)
Returns true if IPADDR contains IPv6 address, otherwise returns false.
Returns NULL if argument evaluates to NULL.
output type
BOOLEAN
Example:
FIELDS_ADD(IS_IPV6(IPADDR('2a00:1450:4010:c05::69')));
PARSEURI(uri_str)
Returns array of URI elements parsed from STRING type argument.
Returns NULL if argument evaluates to NULL.
output type
TUPLE
Example:
FIELDS_ADD(PARSEURI("https://docs.dynatrace.com:443/pages/dynatrace.html#sx-install"));
string_expr1 + string_expr2
string_expr1 || string_expr2
Concatenates string_expr2 to string_expr1.
output type
STRING
FIELDS_ADD(s, s + "! Red fox jumps over lazy dog!");
BASE16_DECODE(string_expr)
UNHEX(string_expr)
Returns base16 decoded string_expr. Returns NULL if string_expr evaluates to NULL.
output type
BYTES
Example:
FIELDS_ADD(encoded:'48656c6c6f20576f726c6421')| FIELDS_ADD(encoded, decoded_bytes:BASE16_DECODE(encoded))| FIELDS_ADD(encoded, decoded_bytes, decoded_str:STRING(decoded_bytes));
BASE16_ENCODE(string_expr)
HEX(string_expr)
Returns base16 encoded string_expr. Returns NULL if string_expr evaluates to NULL.
output type
STRING
Example:
FIELDS_ADD(BASE16_ENCODE('Red fox jumps over brown dog'));
BASE16_ENCODE(bytes)
HEX(bytes)
Returns base16 encoded bytes. Returns NULL if string_expr evaluates to NULL.
output type
STRING
Example:
FIELDS_ADD(BASE16_ENCODE(BYTES([0,1,2])));
BASE64_DECODE(string_expr)
UNBASE64(string_expr)
BASE64DECODE(string_expr)
Returns BYTES of base64 decoded string_expr. Returns NULL if string_expr evaluates to NULL.
output type
BYTES
Example:
FIELDS_ADD(encoded:'SGVsbG8gV29ybGQh')| FIELDS_ADD(encoded, decoded_bytes:BASE64_DECODE(encoded))| FIELDS_ADD(encoded, decoded_bytes, decoded_str:STRING(decoded_bytes));
BASE64_DECODE_STRING(string_expr)
Returns STRING of base64 decoded string_expr. Returns NULL if string_expr evaluates to NULL.
output type
STRING
Example:
FIELDS_ADD(encoded:'SGVsbG8gV29ybGQh')| FIELDS_ADD(encoded, decoded_bytes:BASE64_DECODE(encoded), decoded_string:BASE64_DECODE_STRING(encoded))
BASE64_ENCODE(string_expr)
BASE64(string_expr)
BASE64ENCODE(string_expr)
Returns base64 encoded string_expr. Returns NULL if string_expr evaluates to NULL.
output type
STRING
Example:
FIELDS_ADD(BASE64_ENCODE('Hello World!'));
CONCAT(string_expr, … )
Concatenates expr arguments and returns resulting string. Max number of arguments is 128.
output type
STRING
Example:
FIELDS_ADD(CONCAT('my ', '20 ', '$cents'));
CHARAT(string, pos)
Returns character at pos in the string.
Returns NULL if string evaluates to NULL or if pos points beyond the length of string.
output type
STRING
Example:
FIELDS_ADD(CHARAT("foo",0));
CONTAINS(string1, string2)
Returns true if string1 contains string2. Otherwise returns false.
Returns NULL if either of the arguments evaluates to NULL.
Note
Comparison is case sensitive.
output type
BOOLEAN
Example:
FIELDS_ADD(s, CONTAINS(s, "0"));
ENDS(string_expr1, string_expr2)
Returns true if string_expr1 ends with string_expr2. Otherwise returns false.
Returns NULL if either of the arguments evaluates to NULL.
Note
Comparison is case sensitive.
output type
BOOLEAN
Example:
FIELDS_ADD(s, ENDS(s, "0"));
INDEXOF(str, substr, start_pos)
Returns the index within str of the first occurrence of the specified substr, starting at position start_pos. If start_pos is negative the search is done backward from the start_pos. If not found then -1 is returned.
The start_pos is counted from 0.
output type
INTEGER
Example:
FIELDS_ADD(s, INDEXOF(s,"h",0));
LDIST(string_expr1, string_expr2)
Computes Levenshtein distance between string_expr1 and string_expr2.
output type
INTEGER
Example:
FIELDS_ADD(s1:s, s2:'0ho1') | FIELDS_ADD(s1, s2, LDIST(s1, s2));
LOWER(string)
Converts string to lowercase and returns resulting string.
output type
STRING
Example:
FIELDS_ADD(LOWER('HeLlo WorlD'));
MATCHES(string, pattern_expression)
Returns true if pattern expression matches string. Otherwise returns false.
output type
BOOLEAN
Example:
FIELDS_ADD(MATCHES('hello world', "ALNUM SPACE ALNUM"))
PARSE(string,pattern)
Returns extracted fields of first match of the pattern in the string.
output type
- in case the pattern does not contain any exported matchers then it returns
BOOLEAN, indicating success or failure of parsing.- in case the pattern contains only one exported matcher then it returns matched value as a single value
- in case the pattern contains more than one exported matcher then it returns matched values as
TUPLE
Example:
FIELDS_ADD(strings: "1 out of 10; 2 out of 10; 3 out of 10")| FIELDS_ADD(PARSE(strings, "INT:prefix LD:string INT:suffix"))
PRINTF(format, args…)
Returns a formatted string using format string (based on java.util.Formatter class) and arguments.
output type
STRING
Example:
FIELDS_ADD(PRINTF("Result: %010x string: %s double: %2.2e", i, s, d*10000));
PUNCT(string_expr)
Returns punctuation characters contained in string_expr.
PUNCT(string_expr, count, withSpace)
Returns the first punctuation characters <posix-character-classes> (amount can be specified by the count parameter) from the string (specified by the string_expr parameter) either with or without spaces (specified by the withSpace parameter).
Boolean withSpace includes space character (ASCII 0x20 hex) in search, and is printed out as underscore (ASCII 0x5F hex).
output type
STRING
Example:
FIELDS_ADD(str:"Hi I'm home!") | FIELDS_ADD(PUNCT(str));
REPLACE_STRING(string, target_str, replace_str)
Replaces each substring of string that matches the target_str with the replace_str.
output type
STRING
Example:
FIELDS_ADD(REPLACE_STRING('hello world', 'world', 'space'));
REPLACE_PATTERN(string, pattern_expression, replacement_template_str)
Replaces each substring of string that matches the pattern pattern_expression with the replacement_template_str.
output type
STRING
Example:
FIELDS_ADD(REPLACE_PATTERN("aaa bbb ccc", "'a'+ SPACE b+", "xxx"))
Example:
FIELDS_ADD(REPLACE_PATTERN("label=valueA label=valueB", "'label=' NSPACE:exportedGroup", "modifiedLabel='modified ${exportedGroup}'"))
SH_ENTROPY(string_expr)
Computes Shannon entropy of string_expr
output type
DOUBLE
Example:
FIELDS_ADD(SH_ENTROPY("Hello world!"));
SPLIT(string, pattern_expression)
Splits string around matches of given pattern and returns result as ARRAY of strings.
output type
ARRAY
Example:
FIELDS_ADD(SPLIT('p1;p2;p3', "';'"))
STRLEN(string)
Returns length of string.
output type
INTEGER
Example:
FIELDS_ADD(STRLEN('hello world'));
STARTS(string1, string2)
Returns true if string1 starts with string2. Otherwise returns false.
Note
Comparison is case sensitive.
output type
BOOLEAN
Example:
FIELDS_ADD(STARTS('Hello World!', 'Hell'));
SUBSTR(string, startPos)
Takes substring of string from position startPos to the end of string. If startPos is negative, then the substring is taken from the position relative to the end of string.
output type
STRING
Example:
FIELDS_ADD(SUBSTR('hello world', 6));
SUBSTR(string, startPos, endPos)
Takes substring of str from position startPos to position endPos. Position is counted from 0 (i.e first element is at position 0). If startPos is negative, then substring is taken from position relative to the end of string. If endPos is negative, then substring is taken until position relative to the end of string.
output type
STRING
Example:
FIELDS_ADD(SUBSTR('my 20 $cents', 3,5));
TRIM(string)
Removes leading and trailing whitespaces from string.
output type
STRING
Example:
FIELDS_ADD(str:' hello world ') | FIELDS_ADD(str, TRIM(str));
UNESCAPE(string)
DEESCAPE(string)
Removes escaping from string.
output type
STRING
Example:
FIELDS_ADD(UNESCAPE("\""));
URLDECODE(string)
Returns urldecoded (also known as percent-encoding/decoding) string.
output type
STRING
Example:
FIELDS_ADD(URLDECODE("Hello+world%21"));
URLENCODE(string)
Returns url-encoded string
output type
STRING
Example:
FIELDS_ADD(URLENCODE("Hello world!"));
HTMLUNESCAPE(string)
Unescapes HTML string
output type
STRING
Example:
FIELDS_ADD(HTMLUNESCAPE("<Français>"));
UPPER(string)
Converts string to uppercase.
output type
STRING
Example:
FIELDS_ADD(UPPER('HeLlo WorlD'));
COALESCE(expr1, expr2, exprN)
Evaluates input expressions from left to right and returns the first non-null expression.
output type
the common type of arguments, if arguments are of the same type, otherwise VARIANT.
Example:
FIELDS_ADD(a:string(), b: 'b', c:'c')| FIELDS_ADD(COALESCE(a, b, c))
COLUMN(string_expr)
Selects column by name string_expr. This function can be used when arguments are evaluated dynamically at runtime.
output type
the type of the column selected.
Example:
FIELDS_ADD(ip_addr:COLUMN('ip'));
FLAG(country_code)
Returns Unicode flag symbol of ISO 3166 country_code.
output type
STRING
Example
FIELDS_ADD(country:'Estonia', flag:FLAG('EE'));
HASH64(arg)
Computes 64-bit digest value of argument.
Note
The algorithm is not cryptographically secure.
output type
LONG
Example
FIELDS_ADD(s, hash64:HASH64(s));
REMOVE_CHILD(var_obj, keys …)
Removes selected members (specified by one or more keys) from VARIANT_OBJECT type argument var_obj.
output type
VARIANT_OBJECT
Example: remove members t and s from a variant object:
FIELDS_ADD(vo:VARIANT_OBJECT({i,t,s}))| FIELDS_ADD(REMOVE_CHILD(vo, "t", "s"))
SIZE(arg)
Returns the number of elements in ARRAY, TUPLE, VARIANT_ARRAY, VARIANT_OBJECT or BYTES argument.
output type
INTEGER
Example:
FIELDS_ADD(array, array_size:SIZE(array));
SIZEOF(arg)
Returns the argument size in bytes.
output type
LONG
Example:
FIELDS_ADD(int:i, sizeof_int:SIZEOF(i), tuple, sizeof_tuple:SIZEOF(tuple));
TYPE(arg)
Returns the string identifying data type of the argument.
output type
STRING
Example:
FIELDS_ADD(int_t:TYPE(i),i, time_t:TYPE(t),t, type_array:TYPE(array),array);