String operators

• 1: matches regex operator
• 2: String operators
• 3: The case-insensitive !~ (not equals) string operator
• 4: The case-insensitive !contains string operator
• 5: The case-insensitive !endswith string operator
• 6: The case-insensitive !has string operators
• 7: The case-insensitive !hasprefix string operator
• 8: The case-insensitive !hassuffix string operator
• 9: The case-insensitive !in~ string operator
• 10: The case-insensitive !startswith string operators
• 11: The case-insensitive =~ (equals) string operator
• 12: The case-insensitive contains string operator
• 13: The case-insensitive endswith string operator
• 14: The case-insensitive has string operator
• 15: The case-insensitive has_all string operator
• 16: The case-insensitive has_any string operator
• 17: The case-insensitive hasprefix string operator
• 18: The case-insensitive hassuffix string operator
• 19: The case-insensitive in~ string operator
• 20: The case-insensitive startswith string operator
• 21: The case-sensitive != (not equals) string operator
• 22: The case-sensitive !contains_cs string operator
• 23: The case-sensitive !endswith_cs string operator
• 24: The case-sensitive !has_cs string operator
• 25: The case-sensitive !hasprefix_cs string operator
• 26: The case-sensitive !hassuffix_cs string operator
• 27: The case-sensitive !in string operator
• 28: The case-sensitive !startswith_cs string operator
• 29: The case-sensitive == (equals) string operator
• 30: The case-sensitive contains_cs string operator
• 31: The case-sensitive endswith_cs string operator
• 32: The case-sensitive has_cs string operator
• 33: The case-sensitive hasprefix_cs string operator
• 34: The case-sensitive hassuffix_cs string operator
• 35: The case-sensitive in string operator
• 36: The case-sensitive startswith string operator

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

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.

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

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

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

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

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

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

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

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.

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

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

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

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

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

|…|…|

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

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

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

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

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

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

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.

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

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

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

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

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

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 

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

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

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

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

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

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

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