Select Layer by Attribute clause fails

Question

I have a shapefile and I want to loop through every column and extract selected features with the specified criteria (Column has a P or L).

So my code is like

output = "All.shp"

fields = arcpy.ListFields(output)

for field in fields:
    arcpy.MakeFeatureLayer_management("all.shp", "lyr")
    name = field.name

    clause = name + " LIKE 'P' OR "+ name + " LIKE 'L'"


    arcpy.SelectLayerByAttribute_management("lyr", "NEW_SELECTION", clause)

    arcpy.CopyFeatures_management("lyr", field.name+".shp")

Clause fails with ExecuteError: ERROR 000358: Invalid expression. Tried different things but not working!

Answer 1

1. In a whereclause on a shapefile, you need to place the name of the field between 2 " ".

2. When you use LIKE, you should place wildcard characters to replace a single character (_) or a set of characters (%). Also note that it is case sensitive. In short, this looks like this :

   for field in fields:
   
       arcpy.MakeFeatureLayer_management("all.shp", field.name, """ ("{0}" LIKE '%P%') OR ("{0}" LIKE '%L%' )""".format(field.name))
   
       arcpy.CopyFeatures_management(field.name, field.name+".shp")