CODEWITHSUNDEEP
regexazure-data-explorerkql
checkquery
checkquery

Reputation: 19

Regex: Kusto Query to fetch /extract text after a word

Using Kusto Query, is there a way to extract or fetch the text after a word, "Measure". For example in below string , i would like to fetch 2 values -

string:

SELECT NON EMPTY CrossJoin(Hierarchize(AddCalculatedMembers({DrilldownLevel({[Office View].[Office View].[All]})})), {[Measures].[cubeCount of Sales],[Measures].[Number of Product Categories]}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS , NON EMPTY Hierarchize(AddCalculatedMembers({DrilldownLevel({[Board].[Board].[All]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON ROWS FROM [EZI_NS] WHERE ([Entity].[Entity Schema].&[Total],[Date].[FY Year].&[FY2021],[Date].[FY Month Short].&[Jan],[Type].[Service Type].[All],[DateView].[DateView].&[Periodic]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

Tried using regex, but unable to frame the query in the extract_all function.

Upvotes: 0

Views: 1062

Answers (1)

David דודו Markovitz
David דודו Markovitz

Reputation: 44981

print txt = "SELECT NON EMPTY CrossJoin(Hierarchize(AddCalculatedMembers({DrilldownLevel({[Office View].[Office View].[All]})})), {[Measures].[cubeCount of Sales],[Measures].[Number of Product Categories]}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS , NON EMPTY Hierarchize(AddCalculatedMembers({DrilldownLevel({[Board].[Board].[All]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON ROWS FROM [EZI_NS] WHERE ([Entity].[Entity Schema].&[Total],[Date].[FY Year].&[FY2021],[Date].[FY Month Short].&[Jan],[Type].[Service Type].[All],[DateView].[DateView].&[Periodic]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS"
| project Measures = extract_all(@"\[Measures]\.\[(.*?)]", txt)
Measures
["cubeCount of Sales","Number of Product Categories"]

Fiddle

Upvotes: -1

Related Questions