in~ operator
Applies to: ✅ Azure Data Explorer ✅ Azure Monitor ✅ Microsoft Sentinel
Filters a record set for data with a case-insensitive string.
The following table provides a comparison of the in operators:
| Operator | Description | Case-Sensitive | Example (yields true) |
|---|---|---|---|
in |
Equals to one of the elements | Yes | "abc" in ("123", "345", "abc") |
!in |
Not equals to any of the elements | Yes | "bca" !in ("123", "345", "abc") |
in~ |
Equals to any of the elements | No | "Abc" in~ ("123", "345", "abc") |
!in~ |
Not equals to any of the elements | No | "bCa" !in~ ("123", "345", "ABC") |
Note
Nested arrays are flattened into a single list of values. For example, x in (dynamic([1,[2,3]])) becomes x in (1,2,3).
For further information about other operators and to determine which operator is most appropriate for your query, see datatype string operators.
Case-insensitive operators are currently supported only for ASCII-text. For non-ASCII comparison, use the tolower() function.
Performance tips
Note
When more than 128 search terms are used, text index lookup optimization is disabled, which might lead to reduced query performance.
Note
Performance depends on the type of search and the structure of the data. For best practices, see Query best practices.
When possible, use the case-sensitive in.
Syntax
T | where col in~ (expression, ... )
Learn more about syntax conventions.
Parameters
| Name | Type | Required | Description |
|---|---|---|---|
| T | string |
✔️ | The tabular input to filter. |
| col | string |
✔️ | The column by which to filter. |
| expression | scalar or tabular | ✔️ | An expression that specifies the values for which to search. Each expression can be a scalar value or a tabular expression that produces a set of values. If a tabular expression has multiple columns, the first column is used. The search will consider up to 1,000,000 distinct values. |
Returns
Rows in T for which the predicate is true.
Examples
List of scalars
The following query shows how to use in~ with a comma-separated list of scalar values.
StormEvents
| where State in~ ("FLORIDA", "georgia", "NEW YORK")
| count
Output
| Count |
|---|
| 4775 |
Dynamic array
The following query shows how to use in~ with a dynamic array.
StormEvents
| where State in~ (dynamic(["FLORIDA", "georgia", "NEW YORK"]))
| count
Output
| Count |
|---|
| 4775 |
The same query can also be written with a let statement.
let states = dynamic(["FLORIDA", "georgia", "NEW YORK"]);
StormEvents
| where State has_any (states)
| summarize count() by State
Output
| Count |
|---|
| 4775 |
Tabular expression
The following query shows how to use in~ with an inline tabular expression. Notice that an inline tabular expression must be enclosed with double parentheses.
StormEvents
| where State in~ (PopulationData | where Population > 5000000 | project State)
| summarize count() by State
Output
| State | count_ |
|---|---|
| TEXAS | 4701 |
| ILLINOIS | 2022 |
| MISSOURI | 2016 |
| GEORGIA | 1983 |
| MINNESOTA | 1881 |
| ... | ... |
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 in~ (large_states)
| summarize count() by State
Output
| State | count_ |
|---|---|
| TEXAS | 4701 |
| ILLINOIS | 2022 |
| MISSOURI | 2016 |
| GEORGIA | 1983 |
| MINNESOTA | 1881 |
| ... | ... |