CODEWITHSUNDEEP
regexazureazure-data-explorer
Chris
Chris

Reputation: 53

Azure data explorer kusto regex

I am trying to grab a substring from a queryText column. The queryText column is a SQL query statement. And my goal is to parse and extract specific patterns into a new column called TableName.

parse kind=regex queryText with "[Ff][Rr][Oo][Mm]" TableName

Above is my current Regex statement. It returns all characters after "FROM" or "from". I would like to only grab characters after "FROM" and before the first whitespace or newline. Any idea on what i have to add to the regex expression to do this?

Upvotes: 1

Views: 429

Answers (1)

Yoni L.
Yoni L.

Reputation: 25915

you could use the extract() function.

for example (using the i flag for case-insensitivity):

datatable(input:string)
[
    "select * FROM MyTable\n where X > 1",
    "SELECT A,B,C from MyTable",
    "select COUNT(*) from MyTable GROUP BY X",
    "select * FROM MyTable",
    "select * from [a].[b]",
]
| extend output = extract(@"(?i)from\s+([^\s]+)\s*", 1, input)
input output
select * from [a].[b] [a].[b]
select * FROM MyTable
where X > 1		 MyTable
SELECT A,B,C from MyTable MyTable
select COUNT(*) from MyTable GROUP BY X MyTable
select * FROM MyTable MyTable

Upvotes: 0

Related Questions