Data reviewer regular expression not working

[NeilWrobel]

Hi

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​

[NeilWrobel]

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

[ChrisSmith7]

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.

[ChrisSmith7]

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...

[MichelleJohnson]

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.

[NeilWrobel]

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

[NeilWrobel]

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

[NeilWrobel]

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!!

[MichelleJohnson]

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.

[MichelleJohnson]

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. 

[ChrisSmith7]

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.