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
) )