The case-insensitive has_any string operator

Filters a record set for data with any set of case-insensitive strings. has_any searches for indexed terms, where an indexed term is three or more characters. If your term is fewer than three characters, the query scans the values in the column, which is slower than looking up the term in the term index.

For more information about other operators and to determine which operator is most appropriate for your query, see datatype string operators.

Performance tips

Syntax

T | where col has_any (expression, … )

Parameters

Returns

Rows in T for which the predicate is true.

Examples

List of scalars

The following query shows how to use has_any with a comma-separated list of scalar values.

StormEvents 
| where State has_any ("CAROLINA", "DAKOTA", "NEW") 
| summarize count() by State

Output

Dynamic array

The following query shows how to use has_any with a dynamic array.

StormEvents 
| where State has_any (dynamic(['south', 'north']))
| summarize count() by State

Output

The same query can also be written with a let statement.

let areas = dynamic(['south', 'north']);
StormEvents 
| where State has_any (areas)
| summarize count() by State

Output

Tabular expression

The following query shows how to use has_any with an inline tabular expression. Notice that an inline tabular expression must be enclosed with double parentheses.

StormEvents 
| where State has_any ((PopulationData | where Population > 5000000 | project State))
| summarize count() by State

Output

The same query can also be written with a let statement. Notice that the double parentheses as provided in the last example aren’t necessary in this case.

let large_states = PopulationData | where Population > 5000000 | project State;
StormEvents 
| where State has_any (large_states)
| summarize count() by State

Output

|…|…|