I'm working with a feature class in ArcMap. I want to select features if the text of one field is found in another field.

I have two fields, StreetName and StreetName_1. If StreetName == 'MAIN' and StreetName_1 == "N MAIN ST", I want to select that feature.

I tried the following script, but got an "Invalid expression" error with the SelectLayerByAttribute part:

where_clause = '''"StreetName" IN "StreetName_1"'''

with arcpy.da.SearchCursor(layerName,["StreetName","StreetName_1"]) as cursor:
    arcpy.SelectLayerByAttribute_management(layerName, "NEW_SELECTION", where_clause)
  • The IN operator is not what you want, you want LIKE which can have wildcards, which are different depending on your feature source.. for personal GDB it's an asterisk, for shape and file GDB its a percent, though I think that's not your error, try it without the quotes (they're not needed) where_clause = 'StreetName_1 LIKE StreetName' though I'm not sure on how to put the wildcards in there. – Michael Stimson Aug 28 '17 at 21:45

Depending on your data storage you can specify SQL statements with wildcards, but it gets quite tricky..

Oracle (and PostgreSQL) via SDE uses double pipe concatenation (||) and wildcard % (I have tested this combination as it's the only data I have that is suitable):

where_clause = 'StreetName_1 LIKE \'%\' || StreetName || \'%\''

But as always string comparison needs to be done in either UPPER or LOWER case:

where_clause = 'UPPER(StreetName_1) LIKE \'%\' || UPPER(StreetName) || \'%\''

Unless you're absolutely certain of the case or the database isn't case sensitive (personal GDB isn't case sensitive).

Most other databases use plus concatenation and percent wildcard:

where_clause = 'UPPER(StreetName_1) LIKE \'%\' + UPPER(StreetName) + \'%\''

Personal Geodatabase, from what I've read, uses plus concatenation and asterisk wildcard:

where_clause = 'UPPER(StreetName_1) LIKE \'*\' + UPPER(StreetName) + \'*\''

If one wildcard or concatenation isn't working try the other; there's only 4 combinations of wildcard and concatenation characters to try. Beware though that if StreetName is empty ('') the expression becomes UPPER(StreetName_1) LIKE '%%' which will return any non-empty strings in StreetName_1, to avoid this use CHAR_LENGTH(StreetName) > 0 in your where clause:

where_clause = 'StreetName_1 LIKE \'%\' || StreetName || \'%\' AND CHAR_LENGTH(StreetName) > 0'

For Oracle or PostgreSQL. Read more about SQL in Esri.

  • The first example you gave worked great. Thank you. – Steven C Aug 29 '17 at 15:47

It would require an extra step; however, you could add an additional 'flagging' field and calculate with a function like:

def someFunction(field1, field2):
    if field1 in field2 or field2 in field1:
        return 1

Then you could simply select where !flaggng field! == 1, either manually or via arcpy.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.