azure-application-insightskql

How to find last occurrence of a substring using Kusto Query Language?

Kusto Query Language provides IndexOf function (searches the first occurrence). The question is how to find the last occurrence of some substring.

Solution

  • I guess, the best you can do is (example on how to search for last "cde" in "abcdefabcdef"):

    datatable (name:string, lookup:string)["abcdefabcdef", "cde"]
| project value = strlen(name) - indexof(reverse(name), reverse(lookup)) - strlen(lookup)

    As a function, this would be:

    let lastIndexof = (input:string, lookup: string) {
    iff(indexof(input, lookup) == -1, -1, strlen(input) - indexof(reverse(input), reverse(lookup)) - strlen(lookup))
};