topic Re: Data reviewer regular expression not working in Data Reviewer Support Group Questions

Data reviewer regular expression not working

NeilWrobel — Mon, 12 Oct 2015 13:42:33 GMT

I would like some help with the following:

I have a feature class with the field REF_ID. I would like to constrain what is entered by setting a regular expression for data reviewer to pick up any mismatches.

The REF_ID entries are a semi-colon delimited list of ids.

The REF_IDs can be anything from a single digit starting at '1' and can be up to a maximum of five digits.

The delimited list has no limit other than the character limit of the field.

Here are some examples:

12345 OK

1234 OK

123 OK

12 OK

1 OK

123456 BAD

12345; 54321; 1234; 123 OK

123456; 654321 BAD

12345;54321; BAD

I have done the expression a few different ways and changed it because it seems that Data Reviewer doesn't like something.

I have checked all the expressions in Debuggex and all worked correctly, but just don't work in Data Reviewer.

I was getting all records being returned.

Here is the expression I ended up with which appears to work on records with a single REF_ID entry but not on a delimited list.

i.e. works on '12345' but not on '12345; 54321'

^([1-9](\d)?(\d)?(\d)?(\d)?)(;\s?([1-9](\d)?(\d)?(\d)?(\d)?))*$

Some help with this would be appreciated.

Thanks.
Neil

Python Data Quality Management Data Reviewer

Re: Data reviewer regular expression not working

ChrisSmith7 — Mon, 12 Oct 2015 16:05:08 GMT

Neil,

I believe this may work for you:

^((\d{1,5};*)(\d{1,5};\s*\d{1,5})*)$

I tried it for the following scenarios:

12345 OK

1234 OK

123 OK

12 OK

1 OK

123456 BAD

12345; 54321; 1234; 123 OK

123456; 654321 BAD

12345;54321; BAD

Re: Data reviewer regular expression not working

ChrisSmith7 — Mon, 12 Oct 2015 16:09:14 GMT

It's no good when it's just something like:

12345;

Trying to rework a bit...

Re: Data reviewer regular expression not working

NeilWrobel — Mon, 12 Oct 2015 16:15:23 GMT

But that's good because 12345; would be incorrect as i don't want a trailing semi-colon.

Re: Data reviewer regular expression not working

ChrisSmith7 — Mon, 12 Oct 2015 16:22:00 GMT

A bit inelegant, but this should cover all bases:

(^\d{1,9}$)|(^((\d{1,9};\s*\d{1,9})(\d{1,9};\s*\d{1,9})*)$)

Here's how I tested:

Matched:

123

1234

12345

123;12345

123; 12345

123;12345;12345

123; 12345; 12345

123; 12345;12345

Not matched:

123456

12345;

123;123456

123; 12345; 12345;

Re: Data reviewer regular expression not working

NeilWrobel — Mon, 12 Oct 2015 16:23:13 GMT

Thanks Chris, but the solution you provided is the one we tried in the first place but it doesn't work.

Data Reviewer doesn't like something.

I have had three very experienced developers look at this and they don't know why it won't work.

Re: Data reviewer regular expression not working

ChrisSmith7 — Mon, 12 Oct 2015 16:29:28 GMT

You tried it with the updated suggestion?

(^\d{1,9}$)|(^((\d{1,9};\s*\d{1,9})(\d{1,9};\s*\d{1,9})*)$)

It's a pretty standard regex - if it doesn't work, there may be something specific to Data Reviewers' implementation of regex that makes it special.

Re: Data reviewer regular expression not working

NeilWrobel — Mon, 12 Oct 2015 16:38:07 GMT

Yes. Tried the updated one and doesn't work.

Not great!!

There is nothing particularly complicated with the data.

Re: Data reviewer regular expression not working

NeilWrobel — Mon, 12 Oct 2015 16:41:35 GMT

Did you test it in Data Reviewer or a regex tester??

Re: Data reviewer regular expression not working

ChrisSmith7 — Mon, 12 Oct 2015 17:29:26 GMT

Yeap - I used Regex Hero:

Online .NET Regular Expression Tester and Reference

It's geared towards the .net implementation of regex, but I didn't use anything too complicated - I've used similar expressions in Java in the past.

Re: Data reviewer regular expression not working

NeilWrobel — Tue, 13 Oct 2015 08:30:22 GMT

That's the problem though. The expressions are testing OK but not working in Data Reviewer

Re: Data reviewer regular expression not working

ChrisSmith7 — Tue, 13 Oct 2015 12:59:40 GMT

It must be the implementation with Data Reviewer. In my experience, applications always use a stripped-down version of regex; the expression I created should be pretty basic, but if it's not supported, it's not supported. Let me review the help doc (ArcGIS Desktop) and see if we can make a supported expression.

Re: Data reviewer regular expression not working

ChrisSmith7 — Tue, 13 Oct 2015 19:18:17 GMT

According to the help doc, Data Reviewer uses the ATL engine - https://msdn.microsoft.com/en-us/library/k3zs4axe(v=vs.80).aspx

This isn't supported in Visual Studio 2013 - Microsoft moved it to Codeplex in 2007 and stopped including the libraries in VS since VS 2008. I don't have the Data Reviewer extension, so there's really no way for me to test the expressions. I found an old app from 2006 - Using Regular Expressions in MFC - CodeProject but I can't install it on my work machine.

I looked around for online regex testers that supported the CAtlRegExp flavor, but I couldn't find any - at this point, I would have to guess at the expression without having the ability to test...

Re: Data reviewer regular expression not working

MichelleJohnson — Wed, 14 Oct 2015 01:38:10 GMT

Have you considered using the Execute SQL check to look for any values that end in ";"?

SUBSTRING(REF_ID FROM Char_Length(REF_ID) FOR 1 ) = ';'

Continue to use the Regular Expression check to make sure the values are starting with 1.

Re: Data reviewer regular expression not working

NeilWrobel — Wed, 14 Oct 2015 10:25:56 GMT

Thanks Michelle but I don't think you have fully understood what I am asking.

Re: Data reviewer regular expression not working

NeilWrobel — Wed, 14 Oct 2015 11:01:58 GMT

And the winner is...

^([1-9]\d?\d?\d?\d?)(\b*;\b*[1-9]\d?\d?\d?\d?)*$

Looks like this Microsoft-style regex use \b for whitespace (blank) instead of \s.

https://msdn.microsoft.com/en-us/library/k3zs4axe(v=vs.80).aspx

Re: Data reviewer regular expression not working

NeilWrobel — Wed, 14 Oct 2015 11:05:39 GMT

You guys at ESRI might want to make this a little more obvious in the documentation i.e. in big flashing lights at the start!!

Re: Data reviewer regular expression not working

MichelleJohnson — Wed, 14 Oct 2015 14:53:08 GMT

I'm glad you were able to figure it out and that you had posted this question on GeoNet. This check has always been challenging to figure out the correct expression to use and this will be helpful for other DR users.

Re: Data reviewer regular expression not working

MichelleJohnson — Wed, 14 Oct 2015 14:55:43 GMT

Not sure that we can add the flashing lights, but it is in the documentation, Metacharacters used to build regular expressions—Help | ArcGIS for Desktop.

Re: Data reviewer regular expression not working

ChrisSmith7 — Wed, 14 Oct 2015 14:59:54 GMT

Michelle,

I am not a user of Data Reviewer, but I am familiar with regular expressions - just curious, are there any plans to use another engine in the future? Everything I have seen indicates ATL is deprecated... there's not a lot of information available for users to consult when trying to understand and test the nuances of this particular flavor.