select by attribute LIKE another attribute value

285
4
06-16-2020 12:22 PM
LeilaJackson1
Occasional Contributor

I want to use Select by Attribute to select records where the value in one attribute is LIKE the value in another attribute, so for example:

PIN_TRACKING LIKE '%PIN%'

Where PIN_TRACKING is a list of PINs and I want to identify if PIN is listed in PIN_TRACKING

PIN           PIN_Tracking

1               1,2,3

2               4,5

3               1,2,3

So for the above data it would select record 1 and 3 as the PIN is included in the list in PIN_TRACKING

Thanks for your help!

Tags (1)
0 Kudos
4 Replies
DanPatterson
MVP Esteemed Contributor

not sure where you are doing this... field calculator?, script? within the select by attribute, but conceptually

IF PIN is numeric and PIN_TRACKING is text...

p = 1

pt = '1,2,3'

str(p) in pt  # ---- where p and pt are their associated fields
True

... sort of retired...
0 Kudos
LeilaJackson1
Occasional Contributor

Thanks - I am trying to do it in Select by Attribute. Both are text fields.

0 Kudos
DavidPike
MVP Frequent Contributor

Bit of a stumper actually, I might just add a new field and run a field calculator expression on it to return 0 or 1 or something like that. then select by attributes where the new field = 1.

#pre-logic python

def checker(PIN, PIN_TRACKING):

   if PIN IN PIN_TRACKING.split(","):

       result = '1'

   else:

       result = '0'

   return result

#window

checker(!PIN!, !PIN_TRACKING!)

0 Kudos
JoeBorgione
MVP Esteemed Contributor

I guess you want to do this interactively?  I could see doing it with a python script using a search cursor (untested!!:(

import arcpy
fc = r'C:\path\to\gdb\featureClass'

fields = ['OBJECTID', 'PIN', 'PIN_Tracking']

arcpy.MakeFeaturelayer_management(fc, 'fcLyr')

with arcpy.da.SearchCursor('fcLyr', fields) as cursor:
    for row in cursor:
        select = 'OBJECTID = row[0]'
        if row[1] in row[2]:
            arcpy.SelectLayerByAttribute_management('fcLyr','ADD_TO_SELECTION',select)

I've always thought it would be cool to add a condition to the interactive select by attributes tool...

That should just about do it....