Forum Discussion
Wildcard Matching 2024
Finally got my computer working.
For your three wild cards which I assume as similar meanings as the Like vba operator, something like:
(text,regex)=>
let
regList = Text.ToList(regex),
translate =
List.ReplaceMatchingItems(regList, List.Zip({{"*","#","?","."}, {".*","[0-9]",".","\\."}})),
reg = Text.Combine(translate,""),
fx = Web.Page(
"<script>
var x='"&text&"';
var y=new RegExp('"& reg &"','g');
var b=x.match(y);
document.write(b);
</script>")[Data]{0}[Children]{0}[Children]{1}[Text]{0}
in
fxResults
fnRegexExtr("08erig1", "*8*#*1") => null
fnRegexExtr("qweXxls", "qwe.xls") => null
Hi Ron,
thanks for that function and that you take up this challange. If I assume correctly then you know me and you know what you are getting yourself into. 😁
You are correct VBA.Like is my proof function. And as I need a true/false result I modified the last line of your function:
EDIT: if fx="null" then false else true
If this is wrong, or you have a better solution, tell me. I will make a completely fair comparison.
Here is my test file:
https://www.dropbox.com/scl/fi/hnv86prqiewyajra5h0yr/CompareRegEx.xlsm?rlkey=70ge7534tuugp17ottiw2wwya&st=s41o4w17&dl=1
In C1 is the number of test patterns that are generated if you click the Generate button.
The query steps and used VBA codes are exactly the same for both queries to obtain a comparable result (as best we can do). If you have any concerns about how I did it, tell me. I only want to make a fair comparison of both methods, nothing else.
In column O we can see there are a lot of cases where your function fails, more then 50% is wrong.
Once you have resolved these problems, increase the number of test patterns to 10000, click the Generate button, a blink later they are generated. Click the refresh button above TestCompareWildcards, my function need around a half second.
Click the refresh button above TestRegEx... and take a coffee break.
To be fair, my function is also still not perfect. Therefore, the comparison is not yet really meaningful. But given these time differences, do you think RegEx is a really viable solution?
Andreas.