CODEWITHSUNDEEP
azure-data-explorerkql
Khet_Mng
Khet_Mng

Reputation: 79

Split column string with delimiters into separate columns in azure kusto

I have a column 'Apples' in azure table that has this string: "Colour:red,Size:small".

Current situation:

|-----------------------|
| Apples                |
|-----------------------|
| Colour:red,Size:small |
|-----------------------|

Desired Situation:

|----------------|
| Colour | Size  |
|----------------|
| Red    | small |
|----------------|

Please help

Upvotes: 3

Views: 35920

Answers (2)

Jon
Jon

Reputation: 187

I'll answer the title as I noticed many people searched for a solution.

The key here is mv-expand operator (expands multi-value dynamic arrays or property bags into multiple records):

datatable (str:string)["aaa,bbb,ccc", "ddd,eee,fff"]
| project splitted=split(str, ',')
| mv-expand col1=splitted[0], col2=splitted[1], col3=splitted[2]
| project-away splitted

project-away operator allows us to select what columns from the input exclude from the output.

Result:

+--------------------+
| col1 | col2 | col3 |
+--------------------+
| aaa  | bbb  | ccc  |
| ddd  | eee  | fff  |
+--------------------+

Upvotes: 15

Khet_Mng
Khet_Mng

Reputation: 79

This query gave me the desired results: | parse Apples with "Colour:" AppColour ", Size:" AppSize

Remember to include all the different delimiters preceding each word you want to extract, e.g ", Size". Mind the space between.

This helped me then i used my intuition to customize the query according to my needs:

https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/parseoperator

Upvotes: 2

Related Questions