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

Question

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

JoshuaBixby · Answer

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.

MikeMoehlmann · Answer

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!

D_Hornby_ddh_uni · Answer

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?