Arcpy: How to use Regular Expression with "Select by Attribute"

[MikeMoehlmann]

I want to select featurs from a Feature Class by using "arcpy.management.SelectLayerByAttribute" with a built-in Regular Expression.

The expression should follow this scema: Select all values in the attribute "name" starting with an "A" letter which is followed by a space " " which is followed by a random number series, e. g. "A 213" or "A 1". All other values starting with an "A" should not be selected, e. g. "A Court" or "Anson Street".

I tried many ways, including using the "re" modul, but nothing worked. Here is one easy code example that didn't work:

# SQL-Query with RegExpr
sql_expression = "name LIKE 'A %' AND regexp_like(SUBSTRING(name, 3, 1), '^[0-9]$')"

# Slect
arcpy.management.SelectLayerByAttribute(feature_class_path, "NEW_SELECTION", sql_expression)

Thanks in advance for any help

[DuncanHornby]

I don't believe the variant of SQL used in ArcPro supports regular expressions in the Select by Attribute tool.  You can still use regular expressions but you need to step through your data with a search cursor, then update the selection set.  I provide the code below:

# Import modules
import arcpy
import re

# Initialise objects
layername = "p3"
mapname = "ORN"
fields = ["OBJECTID", "name"]
oids = list()
exp = '[A] \d+'

# Get layer object
p = arcpy.mp.ArcGISProject('current')
m = p.listMaps(mapname)[0]
l = m.listLayers(layername)[0]

# Step through layer using regexp to test text in field
with arcpy.da.SearchCursor(l, fields) as cursor:
    for row in cursor:
        oid = row[0]
        text = row[1]
        mo = re.search(exp, text)
        if mo is not None:
            oids.append(oid)

# Set selection set for layer object
l.setSelectionSet(oids, "NEW")

[MikeMoehlmann]

Thank you for your answer. But this gives me the Error:

Error: list index out of range

Also, do I really have to  define the layername and mapname to do this? Isn't there a way to define my specific feature class and use the function "arcpy.management.SelectLayerByAttribute" with the RegExpr?

[DuncanHornby]

You can do what you want, I have just tried to provide you with clear instructions so you get the overall logic of what you need to do. How you access your layers is up to you. The index error is likely because you have failed to provide the correct map\layer names?

[MikeMoehlmann]

Yes you are right, I just had to adjust the code a little bit. I also added a new field for all Values that follow the RegExp which I can use for my selection. Here is my code that worked with a specific Featur-Class:

# Import modules
import arcpy
import re

# Add a new field
arcpy.management.AddField(feature_class, 
                          "RegExp", 
                          "LONG")

# Define RegExpr
exp = '[A] \d+'

# Use an Update Cursor to Update all values in "RegExp" that follow the scheme
with arcpy.da.UpdateCursor(feature_class, ["OBJECTID", "name", "RegExp"]) as cursor:
    for row in cursor:
        oid = row[0]
        text = row[1]
        mo = re.search(exp, text)
        if mo is not None:
            # Update the value to 1 if condition is fullfilled
            row[2] = "1"
            cursor.updateRow(row)
            
query = ' ("RegExp" = 1)'

arcpy.management.SelectLayerByAttribute(feature_class, 
                                        "NEW_SELECTION", 
                                        query)

Thank you really much for your help! 

[JoshuaBixby]

Although ArcGIS Pro itself doesn't support a standardized regular expression implementation across all data sources, nearly all data sources do support a flavor of it.  Without knowing the workspace or data source type, e.g., file geodatabase, mobile geodatabase, etc...., I can't offer any more specifics.