Check if string starts with prefix list in KQL

user_7845878 0
Viewed 1437

I would like to check in KQL (Kusto Query Language) if a string starts with any prefix that is contained in a list.

Something like:

let MaxAge = ago(30d);
let prefix_list = pack_array(
    'Mr',
    'Ms',
    'Mister',
    'Miss'
);
| where Name startswith(prefix_list)

I know this example can be done with startswith("Mr","Ms","Mister","Miss") but this is not escalable.

kql azure-data-explorer
2 Answers

This function is not available in the Kusto query language, you are welcome to open a suggestion for it in the user feedback form

user_101679140

an inefficient but functional option would be using matches regex - this can work well if the input data set is not too large:

let T = datatable(Name:string)
[
    "hello" ,'world', "Mra", "Ms 2", "Miz", 'Missed'
]
;
let prefix_list = pack_array(
    'Mr',
    'Ms',
    'Mister',
    'Miss'
);
let prefix_regex = strcat("^(", strcat_array(prefix_list, ")|("), ")");
T
| where Name matches regex prefix_regex
Name
Mra
Ms 2
Missed
user_78618070
Related