contains

contains evaluates if the substring that you specify exists within an expression. If the expression contains the substring, contains returns true, and otherwise it returns false.

Syntax

contains(expression, substring, string-comparison-mode)

Arguments

expression

The expression must be a string. It can be the name of a field that uses the string data type, a literal value like '12 Main Street', or a call to another function that outputs a string.

substring

The set of characters to check against the expression. The substring can occur one or more times in the expression.

string-comparison-mode

(Optional) Specifies the string comparison mode to use:

• CASE_SENSITIVE – String comparisons are case sensitive.
• CASE_INSENSITIVE – String comparisons are case insensitive.

This value defaults to CASE_SENSITIVE when blank.

Return type

Boolean

Examples

Default case-sensitive example

The following case-sensitive example evaluates if state_nm contains New.

contains(state_nm, "New")

The following are the given field values.

New York
new york

For these field values, the following values are returned.

true
false

Case-insensitive example

The following case-insensitive example evaluates if state_nm contains new.

contains(state_nm, "new", CASE_INSENSITIVE)

The following are the given field values.

New York
new york

For these field values, the following values are returned.

true
true

Example with conditional statements

The contains function can be used as the conditional statement within the following If functions: avgIf, minIf, distinct_countIf, countIf, maxIf, medianIf, stdevIf, stdevpIf, sumIf, varIf, and varpIf.

The following example sums Sales only if state_nm contains New.

sumIf(Sales,contains(state_nm, "New"))

Does NOT contain example

The conditional NOT operator can be used to evaluate if the expression does not contain the specified substring.

NOT(contains(state_nm, "New"))

Example using numeric values

Numeric values can be used in the expression or substring arguments by applying the toString function.

contains(state_nm, toString(5) )