Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Wildcard Matching 2024

Hi,   First of all, I'm not a BI user, I do support for Excel.   Wildcard comparisons in PQ are a real challenge and everything I've found so far are rudimentary solutions or workarounds to the a...
Need Help
ronrsnfld's avatar
ronrsnfld
Super User
2 years ago

Yes, my regex function is much slower than yours.

  1. You can shorten the last line of my function to "fx <> null" and it will return the logical
  2. The errors with my function are from two issues, both of which can be fixed simply in the code:
    1. Case sensitivity, handled by an argument in the regex call:
      1. "var y=new RegExp('"& reg &"','i');"
    2. Your function apparently needs the entire word to match, and not just part of the word. So this needs the addition of the boundary tokens to the regex translation.

Having said that, it still takes a lot longer than yours.

 

Some of your function errors occur when the data type in text column is a number. These can be handled most simply by making that column text type.

 

Others I need to look at more closely.

 

So far as which to use, that's up to you. Obviously, if your actual use case involves data such as you present in your example table, a corrected routine of yours would be inarguably better. Under different use cases,  the time difference might be irrelevant.

 

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Ron,

 

Thank you for the code change and the tip for my function, I made a few changes, test file updated. I have expanded the generator and included special characters that can appear in file paths.

 

Although the main use is to compare file paths and file names, I would still like to be able to compare unstructured data and thus numbers. I agree with you, this could be avoided by changing the data type. However, if I do it within the function, there are no significant disadvantages, but the function is more universally applicable.

 

At the moment it seems as if I only have to fix one problem with my function. But my experience tells me that I will encounter more.

 

Your routine already works much better, but there seems to be a problem when calling RegEx, the function often fails with an error.

 

Even though I am close to the goal and RegEx is painfully slow, I (and not only me) would appreciate it if you have a solution for this. I am sure that our codes will be used by many followers for a long time to come.

 

Before I wrote this post, I spent a few days researching and having conversations with ChatGPT, Gemini and Copilot... without any really useful results. A wildcard comparison is a real problem and is often requested, but I have not really found any working solutions or code in any high-level languages. There are only partial solutions and a lot of workarounds. RegEx is also often recommended, but there is no RegEx routine that accepts a wildcard pattern anywhere.

 

Thanks again for taking your time into this.

 

Andreas.