Regex trim help in Azure Data Explorer

Question

Regex trim help in Azure Data Explorer

AlonD 21

Hello,

I have a Azure Data Explorer query that returns ID as part of it.
The part of the query is like that:

id = any_id,

Since I'm using this ID as a placeholder in an automation process, I nee to strip the ID from anything but numbers or letters.
Trying to do so with regex failed although I was working according to the documentation below:
https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/trimfunction

The line in the query looks like that:
id = trim(@"[^\w]+",any_id),

Unfortunately the data still comes with characters that are not only letters or numbers.

Assistance will be greatly appreciated :)

Thanks!

KranthiPakala-MSFT 36,007 Reputation points Microsoft Employee

2022-01-24T03:53:01.42+00:00

Hello @AlonD ,

Thanks for the question and using MS Q&A platform.

To better assist on your ask, could you please provide couple of sample values for your ID variable/field and expected output. I'm trying to understand if you want to remove all characters expect [a-z], [A-Z], [0-9] or you would want to remove the substring everything before id value?
AlonD 21 Reputation points

2022-01-24T08:13:06.557+00:00

Thanks @KranthiPakala-MSFT for the reply.

Basically I'd like to remove all characters expect [a-z], [A-Z], [0-9]

Here's an example for an output I'm getting from the query:

711ab733-6098-11c4-cbcc-e23562352354:public:nonprod:linux:linux_iaas:critical

Thanks!

KranthiPakala-MSFT 36,007 Reputation points Microsoft Employee

2022-01-24T03:53:01.42+00:00

Hello @AlonD ,

Thanks for the question and using MS Q&A platform.

To better assist on your ask, could you please provide couple of sample values for your ID variable/field and expected output. I'm trying to understand if you want to remove all characters expect [a-z], [A-Z], [0-9] or you would want to remove the substring everything before id value?
AlonD 21 Reputation points

2022-01-24T08:13:06.557+00:00

Thanks @KranthiPakala-MSFT for the reply.

Basically I'd like to remove all characters expect [a-z], [A-Z], [0-9]

Here's an example for an output I'm getting from the query:

711ab733-6098-11c4-cbcc-e23562352354:public:nonprod:linux:linux_iaas:critical

Thanks!

Answer 1

Hello @AlonD ,

Thank you for clarifying the ask and sharing a sample.

Please try replace_regex() function which should help achieve your requirement. Please refer to below sample.

let str = "711ab733-6098-11c4_cbcc-e23562352354:public:nonprod:linux:linux_iaas:critical";  
print trimmed_str = replace_regex(str, @"[^0-9A-Za-z]", "")

RESULT:

Hope this will help. Please let us know if any further queries.

------------------------------

Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
Want a reminder to come back and check responses? Here is how to subscribe to a notification
If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

KranthiPakala-MSFT 36,007 Reputation points Microsoft Employee

2022-01-28T02:00:30.957+00:00

Hello @AlonD ,

Just checking in to see if the above answer helped. If this answers your query, do click Accept Answer and Up-Vote for the same. And, if you have any further query do let us know.
AlonD 21 Reputation points

2022-01-28T21:16:15.82+00:00

This works perfectly, Thanks so much! :-D

Regex trim help in Azure Data Explorer

0 additional answers

Activity