Scalar operators

• 1: Bitwise (binary) operators
• 2: Datetime / timespan arithmetic
• 3: Logical (binary) operators
• 4: Numerical operators
• 5: Between operators
• 5.1: The !between operator
• 5.2: The between operator
• 6: in operators
• 6.1: The case-insensitive !in~ string operator
• 6.2: The case-insensitive in~ string operator
• 6.3: The case-sensitive !in string operator
• 6.4: The case-sensitive in string operator
• 7: String operators
• 7.1: matches regex operator
• 7.2: String operators
• 7.3: The case-insensitive !~ (not equals) string operator
• 7.4: The case-insensitive !contains string operator
• 7.5: The case-insensitive !endswith string operator
• 7.6: The case-insensitive !has string operators
• 7.7: The case-insensitive !hasprefix string operator
• 7.8: The case-insensitive !hassuffix string operator
• 7.9: The case-insensitive !in~ string operator
• 7.10: The case-insensitive !startswith string operators
• 7.11: The case-insensitive =~ (equals) string operator
• 7.12: The case-insensitive contains string operator
• 7.13: The case-insensitive endswith string operator
• 7.14: The case-insensitive has string operator
• 7.15: The case-insensitive has_all string operator
• 7.16: The case-insensitive has_any string operator
• 7.17: The case-insensitive hasprefix string operator
• 7.18: The case-insensitive hassuffix string operator
• 7.19: The case-insensitive in~ string operator
• 7.20: The case-insensitive startswith string operator
• 7.21: The case-sensitive != (not equals) string operator
• 7.22: The case-sensitive !contains_cs string operator
• 7.23: The case-sensitive !endswith_cs string operator
• 7.24: The case-sensitive !has_cs string operator
• 7.25: The case-sensitive !hasprefix_cs string operator
• 7.26: The case-sensitive !hassuffix_cs string operator
• 7.27: The case-sensitive !in string operator
• 7.28: The case-sensitive !startswith_cs string operator
• 7.29: The case-sensitive == (equals) string operator
• 7.30: The case-sensitive contains_cs string operator
• 7.31: The case-sensitive endswith_cs string operator
• 7.32: The case-sensitive has_cs string operator
• 7.33: The case-sensitive hasprefix_cs string operator
• 7.34: The case-sensitive hassuffix_cs string operator
• 7.35: The case-sensitive in string operator
• 7.36: The case-sensitive startswith string operator

1 - Bitwise (binary) operators

Kusto support several bitwise (binary) operators between integers:

• binary_and
• binary_not
• binary_or
• binary_shift_left
• binary_shift_right
• binary_xor

2 - Datetime / timespan arithmetic

Kusto supports performing arithmetic operations on values of types datetime and timespan.

Supported operations

• One can subtract (but not add) two datetime values to get a timespan value expressing their difference. For example, datetime(1997-06-25) - datetime(1910-06-11) is how old was Jacques-Yves Cousteau when he died.

• One can add or subtract two timespan values to get a timespan value which is their sum or difference. For example, 1d + 2d is three days.

• One can add or subtract a timespan value from a datetime value. For example, datetime(1910-06-11) + 1d is the date Cousteau turned one day old.

• One can divide two timespan values to get their quotient. For example, 1d / 5h gives 4.8. This gives one the ability to express any timespan value as a multiple of another timespan value. For example, to express an hour in seconds, simply divide 1h by 1s: 1h / 1s (with the obvious result, 3600).

• Conversely, one can multiple a numeric value (such as double and long) by a timespan value to get a timespan value. For example, one can express an hour and a half as 1.5 * 1h.

Examples

Unix time, which is also known as POSIX time or UNIX Epoch time, is a system for describing a point in time as the number of seconds that have elapsed since 00:00:00 Thursday, 1 January 1970, Coordinated Universal Time (UTC), minus leap seconds.

If your data includes representation of Unix time as an integer, or you require converting to it, the following functions are available.

From Unix time

let fromUnixTime = (t: long) { 
    datetime(1970-01-01) + t * 1sec 
};
print result = fromUnixTime(1546897531)

Output

To Unix time

let toUnixTime = (dt: datetime) { 
    (dt - datetime(1970-01-01)) / 1s 
};
print result = toUnixTime(datetime(2019-01-07 21:45:31.0000000))

Output

Related content

For unix-epoch time conversions, see the following functions:

• unixtime_seconds_todatetime()
• unixtime_milliseconds_todatetime()
• unixtime_microseconds_todatetime()
• unixtime_nanoseconds_todatetime()

3 - Logical (binary) operators

The following logical operators can be used to perform comparisons and evaluations:

How logical operators work with null values

Null values adhere to the following rules:

Examples

Equality

The following query returns a count of all storm events where the event type is “Tornado”.

StormEvents
| where EventType == "Tornado"
| count

Output

Inequality

The following query returns a count of all storm events where the event type isn’t “Tornado”.

StormEvents
| where EventType != "Tornado"
| count

Output

Logical and

The following query returns a count of all storm events where the event type is “Tornado” and the state is “KANSAS”.

StormEvents
| where EventType == "Tornado" and State == "KANSAS"
| count

Output

Logical or

The following query returns a count of all storm events where the event type is “Tornado” or “Thunderstorm Wind”.

StormEvents
| where EventType == "Tornado" or EventType == "Thunderstorm Wind"
| count

Output

Null values

The following query shows that null values are treated as false.

print print=iff(bool(null) and true, true, false)

Output

Related content

• case()
• has operator
• in operator
• startswith operator

4 - Numerical operators

The types int, long, and real represent numerical types. The following operators can be used between pairs of these types:

Type rules for arithmetic operations

The data type of the result of an arithmetic operation is determined by the data types of the operands. If one of the operands is of type real, the result will be of type real. If both operands are of integer types (int or long), the result will be of type long.

Due to these rules, the result of division operations that only involve integers will be truncated to an integer, which might not always be what you want. To avoid truncation, convert at least one of the integer values to real using the todouble() before performing the operation.

The following examples illustrate how the operand types affect the result type in division operations.

Comment about the modulo operator

The modulo of two numbers always returns in Kusto a “small non-negative number”. Thus, the modulo of two numbers, N % D, is such that: 0 ≤ (N % D) < abs(D).

For example, the following query:

print plusPlus = 14 % 12, minusPlus = -14 % 12, plusMinus = 14 % -12, minusMinus = -14 % -12

Produces this result:

5 - Between operators

5.1 - The !between operator

Matches the input that is outside of the inclusive range.

!between can operate on any numeric, datetime, or timespan expression.

Syntax

T | where expr !between (leftRange..rightRange)

Parameters

Returns

Rows in T for which the predicate of (expr < leftRange or expr > rightRange) evaluates to true.

Examples

Filter numeric values

range x from 1 to 10 step 1
| where x !between (5 .. 9)

Output

Filter datetime

StormEvents
| where StartTime !between (datetime(2007-07-27) .. datetime(2007-07-30))
| count 

Output

Filter datetime using a timespan range

StormEvents
| where StartTime !between (datetime(2007-07-27) .. 3d)
| count 

Output

5.2 - The between operator

Filters a record set for data matching the values in an inclusive range.

between can operate on any numeric, datetime, or timespan expression.

Syntax

T | where expr between (leftRange..rightRange)

Parameters

Returns

Rows in T for which the predicate of (expr >= leftRange and expr <= rightRange) evaluates to true.

Examples

Filter numeric values

range x from 1 to 100 step 1
| where x between (50 .. 55)

Output

Filter by date

StormEvents
| where StartTime between (datetime(2007-07-27) .. datetime(2007-07-30))
| count

Output

Filter by date and time

StormEvents
| where StartTime between (datetime(2007-12-01T01:30:00) .. datetime(2007-12-01T08:00:00))
| count

Output

Filter using a timespan range

StormEvents
| where StartTime between (datetime(2007-07-27) .. 3d)
| count

Output

6 - in operators

6.1 - The case-insensitive !in~ string operator

Filters a record set for data without a case-insensitive string.

Performance tips

When possible, use the case-sensitive !in.

Syntax

T | where col !in~ (expression, … )

Parameters

Returns

Rows in T for which the predicate is true.

Example

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

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

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

let states = dynamic(["Florida", "Georgia", "New York"]);
StormEvents 
| where State !in~ (states)
| summarize count() by State

Output

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

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

6.2 - The case-insensitive in~ string operator

Filters a record set for data with a case-insensitive string.

Performance tips

When possible, use the case-sensitive in.

Syntax

T | where col in~ (expression, … )

Parameters

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

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

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

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

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

6.3 - The case-sensitive !in string operator

Filters a record set for data without a case-sensitive string.

Performance tips

Syntax

T | where col !in (expression, … )

Parameters

Returns

Rows in T for which the predicate is true.

Example

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

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

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

let states = dynamic(["FLORIDA", "GEORGIA", "NEW YORK"]);
StormEvents 
| where State !in (states)
| summarize count() by State

Output

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

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

6.4 - The case-sensitive in string operator

Filters a record set for data with a case-sensitive string.

Performance tips

Syntax

T | where col in (expression, … )

Parameters

Returns

Rows in T for which the predicate is true.

Examples

List of scalars

The following query shows how to use in with a list of scalar values.

StormEvents 
| where State in ("FLORIDA", "GEORGIA", "NEW YORK") 
| count

Output

Dynamic array

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

let states = dynamic(['FLORIDA', 'ATLANTIC SOUTH', 'GEORGIA']);
StormEvents 
| where State in (states)
| count

Output

Tabular expression

The following query shows how to use in with a tabular expression.

let Top_5_States = 
    StormEvents
    | summarize count() by State
    | top 5 by count_; 
StormEvents 
| where State in (Top_5_States) 
| count

The same query can be written with an inline tabular expression statement.

StormEvents 
| where State in (
    StormEvents
    | summarize count() by State
    | top 5 by count_
    ) 
| count

Output

Top with other example

The following example identifies the top five states with lightning events and uses the iff() function and in operator to classify lightning events by the top five states, labeled by state name, and all others labeled as “Other.”

let Lightning_By_State = materialize(StormEvents
    | summarize lightning_events = countif(EventType == 'Lightning') by State);
let Top_5_States = Lightning_By_State | top 5 by lightning_events | project State; 
Lightning_By_State
| extend State = iff(State in (Top_5_States), State, "Other")
| summarize sum(lightning_events) by State 

Output

Use a static list returned by a function

The following example counts events from the StormEvents table based on a predefined list of interesting states. The interesting states are defined by the InterestingStates() function.

StormEvents 
| where State in (InterestingStates()) 
| count

Output

The following query displays which states are considered interesting by the InterestingStates() function.

.show function InterestingStates

Output

Related content

• in~ operator
• !in~ operator
• !in operator
• contains_cs operator
• has_cs operator

7 - String operators

7.1 - matches regex operator

Filters a record set based on a case-sensitive regular expression value.

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

Syntax

T | where col matches regex (expression)

Parameters

Returns

Rows in T for which the predicate is true.

Example

StormEvents
| summarize event_count=count() by State
| where State matches regex "K.*S"
| where event_count > 10
| project State, event_count

Output

7.2 - String operators

Kusto Query Language (KQL) offers various query operators for searching string data types. The following article describes how string terms are indexed, lists the string query operators, and gives tips for optimizing performance.

Understanding string terms

Kusto indexes all columns, including columns of type string. Multiple indexes are built for such columns, depending on the actual data. These indexes aren’t directly exposed, but are used in queries with the string operators that have has as part of their name, such as has, !has, hasprefix, !hasprefix. The semantics of these operators are dictated by the way the column is encoded. Instead of doing a “plain” substring match, these operators match terms.

What is a term?

By default, each string value is broken into maximal sequences of alphanumeric characters, and each of those sequences is made into a term.

For example, in the following string, the terms are Kusto, KustoExplorerQueryRun, and the following substrings: ad67d136, c1db, 4f9f, 88ef, d94f3b6b0b5a.

Kusto: ad67d136-c1db-4f9f-88ef-d94f3b6b0b5a;KustoExplorerQueryRun

Kusto builds a term index consisting of all terms that are three characters or more, and this index is used by string operators such as has, !has, and so on. If the query looks for a term that is smaller than three characters, or uses a contains operator, then the query will revert to scanning the values in the column. Scanning is much slower than looking up the term in the term index.

Operators on strings

The following abbreviations are used in this article:

• RHS = right hand side of the expression
• LHS = left hand side of the expression

Operators with an _cs suffix are case sensitive.

Performance tips

For better performance, when there are two operators that do the same task, use the case-sensitive one. For example:

• Use ==, not =~
• Use in, not in~
• Use hassuffix_cs, not hassuffix

For faster results, if you’re testing for the presence of a symbol or alphanumeric word that is bound by non-alphanumeric characters, or the start or end of a field, use has or in. has works faster than contains, startswith, or endswith.

To search for IPv4 addresses or their prefixes, use one of special operators on IPv4 addresses, which are optimized for this purpose.

For more information, see Query best practices.

For example, the first of these queries will run faster:

StormEvents | where State has "North" | count;
StormEvents | where State contains "nor" | count

Operators on IPv4 addresses

The following group of operators provide index accelerated search on IPv4 addresses or their prefixes.

7.3 - The case-insensitive !~ (not equals) string operator

Filters a record set for data that doesn’t match a case-insensitive string.

The following table provides a comparison of the == (equals) operators:

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

Performance tips

When possible, use the case-sensitive !=.

Syntax

T | where column !~ (expression)

Parameters

Returns

Rows in T for which the predicate is true.

Example

StormEvents
| summarize event_count=count() by State
| where (State !~ "texas") and (event_count > 3000)
| project State, event_count

Output

7.4 - The case-insensitive !contains string operator

Filters a record set for data that doesn’t include a case-sensitive string. !contains searches for characters rather than terms of three or more characters. The query scans the values in the column, which is slower than looking up a term in a term index.

Performance tips

When possible, use the case-sensitive !contains_cs.

Use !has if you’re looking for a term.

Syntax

Case insensitive syntax

T | where Column !contains (Expression)

Parameters

Returns

Rows in T for which the predicate is true.

Example

StormEvents
| summarize event_count=count() by State
| where State !contains "kan"
| where event_count > 3000
| project State, event_count

Output

7.5 - The case-insensitive !endswith string operator

Filters a record set for data that excludes a case-insensitive ending string.

Performance tips

When possible, use the case-sensitive !endswith_cs.

Syntax

T | where col !endswith (expression)

Parameters

Returns

Rows in T for which the predicate is true.

Example

StormEvents
| summarize Events=count() by State
| where State !endswith "is"
| where Events > 2000
| project State, Events

Output

7.6 - The case-insensitive !has string operators

Filters a record set for data that doesn’t have a matching case-insensitive string. !has 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.

Performance tips

When possible, use the case-sensitive !has_cs.

Syntax

T | where column !has (expression)

Parameters

Returns

Rows in T for which the predicate is true.

Example

StormEvents
| summarize event_count=count() by State
| where State !has "NEW"
| where event_count > 3000
| project State, event_count

Output

7.7 - The case-insensitive !hasprefix string operator

Filters a record set for data that doesn’t include a case-insensitive starting string.

For best performance, use strings of three characters or more. !hasprefix 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.

Performance tips

When possible, use the case-sensitive !hasprefix_cs.

Syntax

T | where Column !hasprefix (Expression)

Parameters

Returns

Rows in T for which the predicate is true.

Example

StormEvents
| summarize event_count=count() by State
| where State !hasprefix "N"
| where event_count > 2000
| project State, event_count

7.8 - The case-insensitive !hassuffix string operator

Filters a record set for data that doesn’t have a case-insensitive ending string. !hassuffix returns true if there’s no term inside string column ending with the specified string expression.

Performance tips

When possible, use !hassuffix_cs - a case-sensitive version of the operator.

Syntax

T | where column !hassuffix (expression)

Parameters

Returns

Rows in T for which the predicate is true.

Example

StormEvents
| summarize event_count=count() by State
| where State !hassuffix "A"
| where event_count > 2000
| project State, event_count

Output

7.9 - The case-insensitive !in~ string operator

Filters a record set for data without a case-insensitive string.

Performance tips

When possible, use the case-sensitive !in.

Syntax

T | where col !in~ (expression, … )

Parameters

Returns

Rows in T for which the predicate is true.

Example

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

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

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

let states = dynamic(["Florida", "Georgia", "New York"]);
StormEvents 
| where State !in~ (states)
| summarize count() by State

Output

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

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

7.10 - The case-insensitive !startswith string operators

Filters a record set for data that doesn’t start with a case-insensitive search string.

Performance tips

When possible, use the case-sensitive !startswith_cs.

Syntax

T | where column !startswith (expression)

Parameters

Returns

Rows in T for which the predicate is true.

Example

StormEvents
| summarize event_count=count() by State
| where State !startswith "i"
| where event_count > 2000
| project State, event_count

Output

7.11 - The case-insensitive =~ (equals) string operator

Filters a record set for data with a case-insensitive string.

The following table provides a comparison of the == (equals) operators:

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

Performance tips

When possible, use == - a case-sensitive version of the operator.

Syntax

T | where col =~ (expression)

Parameters

Returns

Rows in T for which the predicate is true.

Example

The State values in the StormEvents table are capitalized. The following query matches columns with the value “KANSAS”.

StormEvents
| where State =~ "kansas"
| project EventId, State

The following table only shows the first 10 results. To see the full output, run the query.

7.12 - The case-insensitive contains string operator

Filters a record set for data containing a case-insensitive string. contains searches for arbitrary sub-strings rather than terms.

Performance tips

When possible, use contains_cs - a case-sensitive version of the operator.

If you’re looking for a term, use has for faster results.

Syntax

T | where col contains_cs (string)

Parameters

Returns

Rows in T for which string is in col.

Example

StormEvents
| summarize event_count=count() by State
| where State contains "enn"
| where event_count > 10
| project State, event_count
| render table

Output

7.13 - The case-insensitive endswith string operator

Filters a record set for data with a case-insensitive ending string.

Performance tips

For faster results, use the case-sensitive version of an operator. For example, use endswith_cs instead of endswith.

Syntax

T | where col endswith (expression)

Parameters

Returns

Rows in T for which the predicate is true.

Example

StormEvents
| summarize Events=count() by State
| where State endswith "sas"
| where Events > 10
| project State, Events

Output

7.14 - The case-insensitive has string operator

Filters a record set for data with a case-insensitive string. has 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.

Performance tips

When possible, use the case-sensitive has_cs.

Syntax

T | where Column has (Expression)

Parameters

Returns

Rows in T for which the predicate is true.

Example

StormEvents
| summarize event_count=count() by State
| where State has "New"
| where event_count > 10
| project State, event_count

Output

7.15 - The case-insensitive has_all string operator

Filters a record set for data with one or more case-insensitive search strings. has_all 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.

Syntax

T | where col has_all (expression, … )

Parameters

Returns

Rows in T for which the predicate is true.

Examples

Set of scalars

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

StormEvents 
| where EpisodeNarrative has_all ("cold", "strong", "afternoon", "hail")
| summarize Count=count() by EventType
| top 3 by Count

Output

Dynamic array

The same result can be achieved using a dynamic array notation.

StormEvents 
| where EpisodeNarrative has_all (dynamic(["cold", "strong", "afternoon", "hail"]))
| summarize Count=count() by EventType
| top 3 by Count

Output

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

let criteria = dynamic(["cold", "strong", "afternoon", "hail"]);
StormEvents 
| where EpisodeNarrative has_all (criteria)
| summarize Count=count() by EventType
| top 3 by Count

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

|…|…|

7.17 - The case-insensitive hasprefix string operator

Filters a record set for data with a case-insensitive starting string.

For best performance, use strings of three characters or more. hasprefix searches for indexed terms, where a 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.

Performance tips

When possible, use the case-sensitive hasprefix_cs.

Syntax

T | where Column hasprefix (Expression)

Parameters

Returns

Rows in T for which the predicate is true.

Example

StormEvents
| summarize event_count=count() by State
| where State hasprefix "la"
| project State, event_count

7.18 - The case-insensitive hassuffix string operator

Filters a record set for data with a case-insensitive ending string. hassuffix returns true if there is a term inside the filtered string column ending with the specified string expression.

Performance tips

When possible, use the case-sensitive hassuffix_cs.

Syntax

T | where Column hassuffix (Expression)

Parameters

Returns

Rows in T for which the predicate is true.

Example

StormEvents
| summarize event_count=count() by State
| where State hassuffix "o"
| project State, event_count

Output

7.19 - The case-insensitive in~ string operator

Filters a record set for data with a case-insensitive string.

Performance tips

When possible, use the case-sensitive in.

Syntax

T | where col in~ (expression, … )

Parameters

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

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

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

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

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

7.20 - The case-insensitive startswith string operator

Filters a record set for data with a case-insensitive string starting sequence.

Performance tips

When possible, use the case-sensitive startswith_cs.

Syntax

T | where col startswith (expression)

Parameters

Returns

Rows in T for which the predicate is true.

Example

StormEvents
| summarize event_count=count() by State
| where State startswith "Lo"
| where event_count > 10
| project State, event_count

Output

7.21 - The case-sensitive != (not equals) string operator

Filters a record set for data that doesn’t match a case-sensitive string.

The following table provides a comparison of the == (equals) operators:

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 column != (expression)

Parameters

Returns

Rows in T for which the predicate is true.

Example

StormEvents
| summarize event_count=count() by State
| where (State != "FLORIDA") and (event_count > 4000)
| project State, event_count

Output

7.22 - The case-sensitive !contains_cs string operator

Filters a record set for data that doesn’t include a case-sensitive string. !contains_cs searches for characters rather than terms of three or more characters. The query scans the values in the column, which is slower than looking up a term in a term index.

Performance tips

If you’re looking for a term, use !has_cs for faster results.

Syntax

Case-sensitive syntax

T | where Column !contains_cs (Expression)

Parameters

Returns

Rows in T for which the predicate is true.

Examples

StormEvents
| summarize event_count=count() by State
| where State !contains_cs "AS"
| count

Output

StormEvents
| summarize event_count=count() by State
| where State !contains_cs "TEX"
| where event_count > 3000
| project State, event_count

Output

7.23 - The case-sensitive !endswith_cs string operator

Filters a record set for data that doesn’t contain a case-insensitive ending string.

Performance tips

Syntax

T | where col !endswith_cs (expression)

Parameters

Returns

Rows in T for which the predicate is true.

Example

StormEvents
| summarize Events=count() by State
| where State !endswith_cs "A"

The following table only shows the first 10 results. To see the full output, run the query.

7.24 - The case-sensitive !has_cs string operator

Filters a record set for data that doesn’t have a matching case-sensitive string. !has_cs 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.

Performance tips

Syntax

T | where column !has_cs (expression)

Parameters

Returns

Rows in T for which the predicate is true.

Example

StormEvents
| summarize event_count=count() by State
| where State !has_cs "new"
| count

Output

7.25 - The case-sensitive !hasprefix_cs string operator

Filters a record set for data that doesn’t have a case-sensitive starting string. !hasprefix_cs 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 column !hasprefix_cs (expression)

Parameters

Returns

Rows in T for which the predicate is true.

Example

StormEvents
| summarize event_count=count() by State
| where State !hasprefix_cs "P"
| count

Output

7.26 - The case-sensitive !hassuffix_cs string operator

Filters a record set for data that doesn’t have a case-sensitive ending string. !hassuffix_cs returns true if there is no term inside string column ending with the specified string expression.

Performance tips

Syntax

T | where column !hassuffix_cs (expression)

Parameters

Returns

Rows in T for which the predicate is true.

Example

StormEvents
| summarize event_count=count() by State
| where State !hassuffix_cs "AS"
| where event_count > 2000
| project State, event_count

Output

7.27 - The case-sensitive !in string operator

Filters a record set for data without a case-sensitive string.

Performance tips

Syntax

T | where col !in (expression, … )

Parameters

Returns

Rows in T for which the predicate is true.

Example

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

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

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

let states = dynamic(["FLORIDA", "GEORGIA", "NEW YORK"]);
StormEvents 
| where State !in (states)
| summarize count() by State

Output

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

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

7.28 - The case-sensitive !startswith_cs string operator

Filters a record set for data that doesn’t start with a case-sensitive search string.

Performance tips

Syntax

T | where column !startswith_cs (expression)

Parameters

Returns

Rows in T for which the predicate is true.

Example

StormEvents
| summarize event_count=count() by State
| where State !startswith_cs "I"
| where event_count > 2000
| project State, event_count

Output

7.29 - The case-sensitive == (equals) string operator

Filters a record set for data matching a case-sensitive string.

The following table provides a comparison of the == operators:

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 == (expression, … )

Parameters

Returns

Rows in T for which the predicate is true.

Example

StormEvents
| where State == "kansas"
| count 

StormEvents
| where State == "KANSAS"
| count 

7.30 - The case-sensitive contains_cs string operator

Filters a record set for data containing a case-sensitive string. contains_cs searches for arbitrary sub-strings rather than terms.

Performance tips

If you’re looking for a term, use has_cs for faster results.

Syntax

T | where col contains_cs (string)

Parameters

Returns

Rows in T for which string is in col.

Example

StormEvents
| summarize event_count=count() by State
| where State contains_cs "AS"

Output

7.31 - The case-sensitive endswith_cs string operator

Filters a record set for data with a case-sensitive ending string.

Performance tips

Syntax

T | where col endswith_cs (expression)

Parameters

Returns

Rows in T for which the predicate is true.

Example

StormEvents
| summarize Events = count() by State
| where State endswith_cs "NA"

Output

7.32 - The case-sensitive has_cs string operator

Filters a record set for data with a case-sensitive search string. has_cs 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.

Performance tips

Syntax

T | where Column has_cs (Expression)

Parameters

Returns

Rows in T for which the predicate is true.

Example

StormEvents
| summarize event_count=count() by State
| where State has_cs "FLORIDA"

Output

Since all State values are capitalized, searching for a lowercase string with the same value, such as “florida”, won’t yield any results.

StormEvents
| summarize event_count=count() by State
| where State has_cs "florida"

Output

7.33 - The case-sensitive hasprefix_cs string operator

Filters a record set for data with a case-sensitive starting string.

For best performance, use strings of three characters or more. hasprefix_cs searches for indexed terms, where a 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.

Performance tips

Syntax

T | where Column hasprefix_cs (Expression)

Parameters

Returns

Rows in T for which the predicate is true.

Examples

StormEvents
| summarize event_count=count() by State
| where State hasprefix_cs "P"
| count 

StormEvents
| summarize event_count=count() by State
| where State hasprefix_cs "P"
| project State, event_count

7.34 - The case-sensitive hassuffix_cs string operator

Filters a record set for data with a case-insensitive ending string. hassuffix_cs returns true if there is a term inside the filtered string column ending with the specified string expression.

Performance tips

Syntax

T | where column hassuffix_cs ( expression )

Parameters

Returns

Rows in T for which the predicate is true.

Examples

StormEvents
| summarize event_count=count() by State
| where State hassuffix_cs "AS"
| where event_count > 2000
| project State, event_count

Output

7.35 - The case-sensitive in string operator

Filters a record set for data with a case-sensitive string.

Performance tips

Syntax

T | where col in (expression, … )

Parameters

Returns

Rows in T for which the predicate is true.

Examples

List of scalars

The following query shows how to use in with a list of scalar values.

StormEvents 
| where State in ("FLORIDA", "GEORGIA", "NEW YORK") 
| count

Output

Dynamic array

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

let states = dynamic(['FLORIDA', 'ATLANTIC SOUTH', 'GEORGIA']);
StormEvents 
| where State in (states)
| count

Output

Tabular expression

The following query shows how to use in with a tabular expression.

let Top_5_States = 
    StormEvents
    | summarize count() by State
    | top 5 by count_; 
StormEvents 
| where State in (Top_5_States) 
| count

The same query can be written with an inline tabular expression statement.

StormEvents 
| where State in (
    StormEvents
    | summarize count() by State
    | top 5 by count_
    ) 
| count

Output

Top with other example

The following example identifies the top five states with lightning events and uses the iff() function and in operator to classify lightning events by the top five states, labeled by state name, and all others labeled as “Other.”

let Lightning_By_State = materialize(StormEvents
    | summarize lightning_events = countif(EventType == 'Lightning') by State);
let Top_5_States = Lightning_By_State | top 5 by lightning_events | project State; 
Lightning_By_State
| extend State = iff(State in (Top_5_States), State, "Other")
| summarize sum(lightning_events) by State 

Output

Use a static list returned by a function

The following example counts events from the StormEvents table based on a predefined list of interesting states. The interesting states are defined by the InterestingStates() function.

StormEvents 
| where State in (InterestingStates()) 
| count

Output

The following query displays which states are considered interesting by the InterestingStates() function.

.show function InterestingStates

Output

Related content

• in~ operator
• !in~ operator
• !in operator
• contains_cs operator
• has_cs operator

7.36 - The case-sensitive startswith string operator

Filters a record set for data with a case-sensitive string starting sequence.

Performance tips

Syntax

T | where col startswith_cs (expression)

Parameters

Returns

Rows in T for which the predicate is true.

Example

StormEvents
| summarize event_count=count() by State
| where State startswith_cs "I"
| where event_count > 2000
| project State, event_count

Output