Extract the matching word from the table column

Question

Hi

I have table called "Test" and only one column like "Col1" and it has values like

Col1

---

Test_AA_One
Test_Check_BB_One
Test_Check_Another_CC_One
Test_DD_One
Test_New_EE_One

I want to check if the column value has any ("AA" or "BB" or "CC" or "DD" or "EE") for each row then i have to extract the data like below

Col1 SearchReturnedValue
***** ***********************
Test_AA_One AA
Test_Check_BB_One BB
Test_Check_Another_CC_One CC
Test_DD_One DD
Test_New_EE_One EE

how can we get it.

Answer 1

Hello @ADX ,
Thanks for the ask and using Microsoft Q&A platform .
I think the below query should do the trick . Read more about the mv-expand here .

Expands multi-value dynamic arrays or property bags into multiple records.
mv-expand can be described as the opposite of the aggregation operators that pack multiple values into a single dynamic-typed array or property bag, such as summarize ... make-list() and make-series. Each element in the (scalar) array or property bag generates a new record in the output of the operator. All columns of the input that aren't expanded are duplicated to all the records in the output.
Syntax
T | mv-expand [bagexpansion=(bag | array)] [with_itemindex=IndexColumnName] ColumnName [to typeof( Typename)] [, ColumnName ...] [limit Rowlimit]
T | mv-expand [bagexpansion=(bag | array)] Name = ArrayExpression [to typeof(Typename)] [, [Name =] ArrayExpression [to typeof(Typename)] ...] [limit Rowlimit]

Test
| mv-expand SearchReturnedValue=split(Col1,'_')
| project Col1,SearchReturnedValue
|where SearchReturnedValue in ("AA","BB","CC","DD","EE")

Please do let me know how it goes .
Thanks
Himanshu

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

• 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