Filter functions

This articles goes over the various filter functions that are available for the $filter query option.

General

not

not(value)

Inverts the boolean value and returns the result.

isnull

isnull(value)

Returns the boolean value true if value is null, otherwise false.

isnotnull

isnotnull(value)

Returns the boolean value true if value is not null, otherwise false.

contains

contains(value, args...)

Returns the boolean value true if value equates to any of the arguments provided, otherwise false.

This is converted into the SQL expression (value IN (args...)).

has

has(bitmask, flag)

Returns the boolean value true if flag exists within the bitmask value, otherwise false.

like

like(value, pattern)

Returns the boolean value true if value matches the pattern provided, otherwise false.

This is converted into the SQL expression (value LIKE pattern), consult the documentation for the system's SQL dialect for more information about the pattern itself.

Math

add

add(value1, value2)

Adds value1 to value2 and returns the result.

sub

sub(value1, value2)

Subtracts value2 from value1 and returns the result.

mul

mul(value1, value2)

Multiplies value1 with value2 and returns the result.

div

div(value1, value2)

Divides value1 by value2 and returns the result.

mod

mod(dividend, divisor)

Calculates the remainder of the Euclidian division of the dividend by the divisor and returns the result.

round

round(value)

Rounds value and returns the result.

ceiling

ceiling(value)

Calculates the smallest integral value that is greater than or equal to value and returns the result.

floor

floor(value)

Calculates the largest integral value that is less than or equal to value and returns the result.

String

strcontains

strcontains(value1, value2)

Returns the boolean value true if value1 contains the string value2, otherwise false.

endswith

endswith(value1, value2)

Returns the boolean value true if value1 ends with the string value2, otherwise false.

startswith

startswith(value1, value2)

Returns the boolean value true if value1 starts with the string value2, otherwise false.

length

length(value)

Returns the string length of value.

indexof

indexof(value1, value2)

Returns the zero-based character index of where value2 occurs in value1 or -1 if it does not occur at all.

substring

substring(value, start[, end])

Returns a substring of value starting at start and ending at end if provided.

tolower

tolower(value)

Transforms value to lowercase and returns the result.

toupper

toupper(value)

Transforms value to uppercase and returns the result.

trim

trim(value)

Trims any preceding and trailing whitespace from value and returns the result.

concat

concat(value, args...)

Concatenates multiple string values together and returns the result.

Date

year

year(value)

Returns the year component of the provided value.

month

month(value)

Returns the month component of the provided value.

day

day(value)

Returns the day component of the provided value.

hour

hour(value)

Returns the hour component of the provided value.

minute

minute(value)

Returns the minute component of the provided value.

second

second(value)

Returns the second value of the provided value.

date

date(value)

Returns the date component of the provided value.

time

time(value)

Returns the time component of the provided value.

now

now()

Returns the current date/time.