if it contains

Question

Hi Team,

I have below data, I have two condition here.

1. I want to keep only those data in which we have 5 times either 2 or 9  as 22222 or 99999 and

2. I also want to keep those data in which we have 2 starting letter as alpha and 3 numeric after that for exp ab345, wr765 etc.

apart from this just delete all data.

COD 8ab345ab765wr345asd234345az2222299999222222999999df345fv567tz6782223478867222226  za567789azqw211qw000

Qiu · Accepted Answer

@mmustkee

I think I can help you with this one.😁

Qiu · Accepted Answer

@mmustkee 
A filter tool would do it since I already flag the entries.

MarqueeCrew · Accepted Answer

@mmustkee ,

the regular expression that @Qiu provided you can be used in the filter tool as:  regex_match() without the IF expression. That eliminates the formula and select tools. But, I did notice that his formula only works on 5 character codes.  In your examples 222226 or if ab1234 existed it won't match.  I assume that the 5 character codes is also an implied requirement.

I was wondering if this would perform faster as:

[cod 8] in ("222222","999999") OR

( !IsInteger(left([cod 8],2)) AND IsInteger(Right([cod 8],3)) )

So I created 1,000,000 rows of data and tested this FILTER expression against @Qiu 's FILTER expression (modified to just be a filter)

REGEX_Match([cod 8], "[2,9]{5}|\w{2}\d{3}")

RegEx, something that I advocate, takes more resources to execute.  It provides great ease in working with unstructured data.  In some cases, it is easier to craft than string functions.  In this case it is more concise if you understand what it says.  But what about performance?

With one million records tested the difference in timing is under 1 second.  but the profile in milliseconds is substantial.  The RegEx solution takes 1,451 ms and my alternate solution takes 670 ms.

Using a regular expression, you should expect 2-3 times the processing time for each function.  In this case I did use an evaluation of 3 expressions to equal the single RegEx statement.

I just wanted to offer an alternate solution for the community.

Cheers,

Mark