Definition Queries and Regular Expressions Ranges

[BrandonMcAlister]

I have ArcGIS Pro 3.2 Advanced with no additional licenses. I want to create a definition query to query my address2 field for any upper and lower cases letters or numbers. I am trying to isolate where blank cells and spaces are improperly imported from excel. I am currently messing around with a definition query to get my table view to show the selection I want. I have 4,460 records and 3,857 are blank. 

When I construct my definition query:

where address2 NOT LIKE '%([A-Za-z0-9])%'

This should query out value in address2 that has an upper or lower case letter A through Z and any number 0 through 9. 

but it only excludes 2 records, is ArcGIS Pro unable to support ranges and regular expressions?

I have tried several variations of my range to include word boundary \b, and + signs.

I currently have it written the most difficult way of each individual letter and number........

Edit: This is in a file geodatabase, and I tried to construct the definition query by adding a standalone table to the map and building it in the sql expression builder window. Specifically using the radio button to write out my own sql expression.

[Bud]

1. What kind of geodatabase? For example, Oracle 18c 10.7.1 enterprise geodatabase.
2. Can you share some fake data as a screenshot, excel spreadsheet, or a zipped file GDB or zipped mobile GDB?

[BrandonMcAlister]

@Bud 

see attached files, that is a small portion of the dataset

[JoshuaBixby]

The SQL LIKE predicate is a form of pattern matching, but it is not regular expression pattern matching.  Although support for SQL LIKE is much more consistent across database platforms than regular expressions, there may be cases where it is implementation specific, so it is best to state what data source you are working with.

UPDATE:  An update to the question indicates you are working with file geodatabase data.

As I mentioned already, SQL LIKE is not regular expression, and regular expression patterns will not work with LIKE, especially since Esri does not support character-class wildcards with the file geodatabase implementation of LIKE.

Although undocumented, Esri does support a fairly robust regular expression implementation in the file geodatabase with the SIMILAR TO predicate.  Oddly, they even kept the wildcard the same as LIKE even though actual regular expression uses different wildcards.

[JoshuaBixby]

@BrandonMcAlister, I see you posted Regular Expressions in Definition Queries .  Seeing that most data sources, including file geodatabases, already include support for regular expressions; did you figure out how to get this working?

[BrandonMcAlister]

@JoshuaBixby 

No, I have an understanding that adding a layer to a map and constructing a definition query for the map layer to query the data is not supported. 

I can use a regular expression to access data in a file geodatabase, and manipulate it with python. I am unable to use a regular expression on a layer within a map. That appears to be unsupported.

If you can do it, please use the sample data attached and show me the results. I am eager to find out where I am going wrong.

[JoshuaBixby · Accepted solution]

I downloaded your file geodatabase and added a definition query using regular expression (SIMILAR TO for FGDB), and it works.  All you have to do is replace "LIKE" with "SIMILAR TO" in your expression.

[BrandonMcAlister]

@JoshuaBixby 

Alright, I read your previous post around when you posted it. I swear I tried it and it didn't work... it works now. Thank you for your assistance