Operator

Name

Description

Example

Nullability

abs

Absolute Value

abs(number)

Find the absolute value of a number.

Parameter:

·         number – The number to get the absolute number of.

abs(-47.11) = 47.11

array

Array

array(size,value)

Create an array of specified size and initialize with the given value.

Parameters:

·         size – Size of the array.

·         value – Array elements.

array(5, 1.1)

a

atan

Arc Tangent

atan(ordinate, abscissa)

Get the inverse tangent of a number.

Parameters:

·         ordinate – The ordinate coordinate.

·         abscissa – The abscissa coordinate (optional).

atan(1.0) = 0.785398

ceil

Ceiling

ceil(number)

Returns the smallest double value that is greater than or equal to the argument and is equal to a mathematical integer.

Parameter:

·         number – The number to get the ceiling value of.

ceil(4.7) = 5.0

clock

Clock

Returns the current system Date/Time value.

clock()

concat

Concatenate

concat(text1,text2)

Joins two text values.

Parameters:

·         text1 – First text to join.

·         text2 – Second text to join.

concat(‘olle’,’pelle’) = ‘olle pelle’

cos

Cosine

cos(number)

The natural logarithm (base e) of a real value.

Parameter:

·         number – A number to take the natural logarithm of .

cos(0.0) = 1.0

cosh

Hyperbolic Cosine

cosh(number)

Get the hyperbolic cosine of the number.

Parameter:

·         number – The number to get the hyperbolic cosine of.

cosh(0.0) = 1.0

cot

Cotangent

cot(number)

Get the cotangent of the number.

Parameter:

·         number – The number to get the cotangent of.

dateDiff

Date Difference

dateDiff(startDatetime,endDatetime,unit)

Calculates the difference in whole units between two time values, the return value is positive if endDatetime comes after startDatetime, and is negative if endDatetime comes before startDatetime.

Valid units are ‘DAYS’, ‘HOURS’, ‘MINUTES’, ‘SECONDS, ‘’MILLISECONDS’, ‘MICROSECONDS’, AND ‘NANOSECONDS’.

Parameters:

·         startDatetime – The first (later) Date/Time value.

·         endDatetime – The second (earlier) Date/Time value.

·         unit – The time unit to use.

dateDiff(#2019-06-17#,#2019-06-14#,’DAYS’) = 3

dateDiff2

Date Difference

Calculates the difference in fraction units between two time values, the return vallue is positive if endDatetime comes after startDatetime, and is negative if endDatetime comes before startDatetime.

Valid units are ‘DAYS’, ‘HOURS’, ‘MINUTES’, ‘SECONDS, ‘’MILLISECONDS’, ‘MICROSECONDS’, AND ‘NANOSECONDS’.

Parameters:

·         startDatetime – The first (later) time value.

·         endDatetime – The second (earlier) time value.

·         unit – The time unit to use.

dateDiff2(#2019-06-17T12:00:00#,#2019-06-14T00:00:00#,’DAYS’) = 3.5

datePart

Date Part

Returns a specified part of a time value, the result is an integer value.

Valid units are ‘DAYS’, ‘HOURS’, ‘MINUTES’, ‘SECONDS, ‘’MILLISECONDS’, ‘MICROSECONDS’, AND ‘NANOSECONDS’.

Parameters:

·         datetime – The Date/Time value.

·         part – The time part to get.

datePart(#1973-07-23#,’YEARS’) = 1973

dateTrunc

Date Truncate

dateTrunc(datetime,datetimePart)

Truncates the specified time value to the accuracy specified by the time_part.

Valid units are ‘DAYS’, ‘HOURS’, ‘MINUTES’, ‘SECONDS, ‘’MILLISECONDS’, ‘MICROSECONDS’, AND ‘NANOSECONDS’.

Parameters:

·         datetime – The Date/Time value.

·         datetimepart – The Date/Time part to be truncated.

dateTrunc(#1973-07-23T12:34:56#,’YEARS’) = #1973-01-01T00:00:00#

exp

Exponential

exp(number)

Find the value of e raised to the power of a number.

Parameter:

·         number – The power that e is raised to.

exp(0.0) = 1.0

find

Find

find(findText,withinText,startPosition)

Returns the starting position of one string within another string, given a starting position.

Parameters:

·         findText – The text to find.

·         withinText – The text to search witihin.

·         startPosition – Start the search from this position (optional).

find(‘ab’,’drabant’) = 3

floor

Floor

floor(number)

Returns the largest real value that is less than or equal to the argument and is equal to a mathematical integer.

Parameter:

·         number – The number to get the flloor value of.

floor(4.7) = 4.0

get

Get

get(array,position)

Gets the nth element of the array.

Parameters:

·         array – Array of items.

·         position – Array element at this position.

NOTE: Index of the array starts with 0.

get(array(5,1.1),1) = 1.1

a

if

If

if(condition,exprIfTrue,expIfFalse)

The function provides branching capability. If condition is true, then it returns exprIfTrue, else it returns exprIfFalse.

Parameters:

·         condition – A value or logical expression that can be evaluated as true or false.

·         exprIfTrue – The value to return when condition evaluates to true.

·         exprIfFalse – The value to return when condition evaluates to false.

if(a < b, a,b)

a

ifNull

ifNull

ifNull(expression,altValue)

The ifNull function returns the specified value if the expression is null, otherwise returns the expression.

Parameters:

·         expression – The expression to test whether it is null.

·         altValue – The value to return if the expression is null.

ifNull(null, ‘b’) = ‘b’

a

index

Index

index(array,text)

Sorts the input array and outputs a lookup index.

Parameters:

·         array – Array of items.

·         text – The order which the array should be sorted, the valid texts are ‘asc’ for ascending order or ‘desc’ for descending order.

index(array(3,#1973-07-23#),’asc’)

intpow

Integral Power

intpow(number,power)

Raise a number to a power.

Parameters:

·         number – Number to raise a power.

·         power - The power to raise a number to.

intpow(2.0, 3.9) = 8.0

invert

Invert

Inverts a lookup index. Since the index function returns an inverse permutation, you can apply the invert function which will turn it into a forward permutation (or rank).

left

Left

left(text,numofChars)

Returns the leftmost characters from a string producing a new string.

Parameters:

·         text – The text from which to extract characters.

·         numofChars – Number of characters to be picked from the left.

left(‘olle’, 3) = ‘oll’

len

Length

len(value)

Returns the number of characters in a string or the number of elements in an array.

Parameter:

·         value – String or array to find the length of.

len(‘olle’) = 4

ln

Logarithm

ln(number)

The natural logarithm (base e) of a real value.

Parameter:

·         number – A number to take the natural logarithm of.

ln(1) = 0

log

Logarithm

log(number)

Logarithm with base 10.

Parameter:

·         number – Number of which you want the logarithm.

log(1000.0) = 3.0

logn

Logarithm

logn(number,logBase)

Returns the Log Based N of Input.

Parameters:

·         number – Number of which you want the logarithm.

·         logBase – Base of the logarithm.

logn(4711.0,4711.0) = 1.0

lower

Lower

lower(text)

Convert text to lower case.

Parameter:

·         text – Text to change case to lower.

lower(‘OLLE’) = ‘olle’

a

max

Maximum

Maximum of the two numbers.

max(11.0, 47.0) = 47.0

mid

Mid

mid(string,startPosition,numofChars)

Returns the characters from the middle of a text string, given a starting position and length.

Parameters:

·         string – The original string.

·         startPosition – Starting position in string.

·         numofChars – Length of the substring.

mid(‘olle’, 2,2) = ‘ll’

min

Minimum

Minimum of the two numbers.

min(47.0, 11.0) = 11.0

pow

Power

pow(number,power)

Raise a number to a power.

Parameters:

·         number – Number to raise a power.

·         power – The power to raise a number to.

pow(-2.0, 7.0) = -128.0

proper

Proper

proper(text)

Converts a text to proper case; the first letter in each word in uppercase, and all other letters in the lower case.

Parameter:

·         text – The text to make as a proper case.

proper(‘olle asp’) = ‘Olle Asp’

a

random

Random

random(minimumValue,maximumValue)

Returns a random number with a positive sign.

Parameters:

·         minimumValue – Minimum value or a random number (optional).

·         maximumValue - Maximum value or a random number (optional).

random(12.0) get a floating-point random number greater than or equal to 0.0 and less than 12.0

replaceAll

Replace All

replaceAll(string,oldText,newText)

Replaces all occurences of the pattern with the replacement string.

Parameters:

·         string – The original string.

·         oldText – The string to be replaced.

·         newText – The new replacement string.

replaceAll(‘axa’, ‘a’, ‘b’) = ‘bxb’

a

replaceFirst

Replace First

replaceFirst(string,oldText,newText)

Replaces the first occurrence of the pattern with the replacement string.

Parameters:

·         string – The original string.

·         oldText – The string to be replaced.

·         newText – The new replacement string.

replaceFirst(‘axa’, ‘a’, ‘b’) = ‘bxa’

a

right

Right

right(text,numofChars)

Returns the rightmost characters from a string producing a new string.

Parameters:

·         text – The text from which to extract characters.

·         numofChars – Number of characters to be picked from the right.

right(‘olle’, 3) = ‘lle’

rnd

Rnd

rnd(scaleValue)

Returns a random number with a positive or negative sign depending on scale value.

Parameter:

·         scaleValue – Positive scaleValue will result in a number that is maximum up to it and the negative scaleValue will result in a number that is minimum to it.

round

Round

round(number,digits)

Round a number to a given number of digits.

Parameters:

·         number – The number to round.

·         digits – The place at which number should be rounded (optional).

round(47.11) = 47.0

set

Set

set(array,position,newvalue)

Sets the nth element of the array.

Parameters:

·         array – Array of items.

·         position – Array element at this position.

·         newvalue – New value of the element.

NOTE: Index of first element starts with 0.

set(array(5, 1.1), 1, 2.2)

a

sign

Sign

sign(number)

Get the sign of a number, returns one if positive, negative one if negative, and zero if zero.

Parameter:

·         number – The number to get the sign of.

sign(7) = 1

sin

Sine

sin(number)

Get the sine of the number.

Parameter:

·         number – The number to the sine of.

sinh

Sinus Hyperbolic

sinh(number)

Get the sinus hyperbolic of the number.

Parameter:

·         number – The number to the sinus hyperbolic of.

sinh(0.0) = 0.0

sort

Sort

Applies a lookup index to an array.

sqr

Square

sqr(number)

Returns square of the number.

Parameter:

·         number – The number to get the square of.

sqr(3) = 9

sqrt

Square Root

sqrt(number)

Returns square root of the number.

Parameter:

·         number – The number to get the square root of.

sqrt(9.0) = 3.0

tan

Tangent

tan(number)

Get the tangent of the number.

Parameter:

·         number – The number to get the tangent of.

tan(0.0) = 0.0

trim

Trim

trim(text)

Get the input text stripped of leading or following spaces.

Parameter:

·         text – The text to be stripped of leading or following spaces.

trim(‘ olle ’) = ‘olle’

a

trunc

Truncate

trunc(number,digits)

Truncate a number to a given precision.

Parameters:

·         number – The number to truncate.

·         digits – The precision of the truncation (optional and the default is 0).

trunc(47.11) = 47.0

upper

Upper

upper(text)

Convert text to upper case.

Parameter:

·         text – Text to change case to upper.

upper(‘olle’) = ‘OLLE’