String Search with unknown/special characters

Question

Hi Community - I am trying to find a tool which will find match cases when our product matches certain criteria. Below are a couple of examples, the length of the product code is specific and I need to be able to filter in/out when there is a match. For example, the first product code could end with any character (numeric or text) and there is just 1 variable to consider, however in the 3rd example I need the middle components to be present in a specific location on the product code (so the 2 must be the 9th character of the code, and then US must be characters 11 and 12.)

ECD50G-1*ECD60G-0-*********VN3*****2*US****

Thanks for the help!

MarqueeCrew · Answer

You can combine all match criteria into a single if then else structure. For readability and maintenance and sometimes performance you can use filters and then bring the data back together.

I'd have to review all of your data to choose the logic that works best.  If the last charter is a choice, then right() is a great string function.  The use of pattern matching and regex_match() helps for unstructured data, but instead of packing all the choices into one expression, i still like multiple expressions and filters.

this makes testing and maintenance easier.

cheers,

mark

DanielHowes · Answer

Thanks for the quick response. Those were a couple of examples, I have a couple of thousand unique lines to get through. Is there a quicker way to join in the variations and dynamically search the string for any of the baseline configurations?

MarqueeCrew · Answer

hint:

substring([product code],8,1) = "2"

and

substring([product code],10,2) = "US"

cheers,

mark