String operators

Applies to: ✅ Azure Data Explorer ✅ Azure Monitor ✅ Microsoft Sentinel

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.