Selecting attributes which have comma delimiter

Question

I'm currently working on a large dataset of roads. In which i have to check if the roads in 1:250 000 have the same road number as those in 1:50 000. Because of alot of generalization I have to make a radius serch in 1:250 000 to be sure i have gotten the road into the spatial join. For this to work, i used field mapping parameter "join" with "," as a delimiter. Since this doesen't work with numbers, I converted the field to text.

The problem I'm having now is that I cannot select specific numbers from the new field. It will only work on the first, so when i try to check if the road number from 1:250 000 is equal to 1:50 000 it will only go through the 1st number in the column and not the rest that follows after the comma delimiter. Does anyone have any idea how i might check all the numbers that has been converted to text, against one specific number?

Answer 1

Script below will select rows where there is a match between any of the comma-delimited numbers in one field and an integer in another field. Execute code in the Python window.

def checkNumbers(layer,textfield,checkfield):
    oidlist=[]
    with arcpy.da.SearchCursor(layer,['OID@',textfield,checkfield]) as cursor:
        for row in cursor:
            templist=row[1].replace(" ","").split(',')
            if str(row[2]) in templist:
                oidlist.append(row[0])
    sql="{0} IN({1})".format(arcpy.AddFieldDelimiters(layer,'OBJECTID'),", ".join(map(str,oidlist)))
    arcpy.SelectLayerByAttribute_management(in_layer_or_view=layer, 
                                           where_clause=sql)

Call the function with for example:

checkNumbers("Points111","VEGNUMMER","IntegerField")

Or as standalone script:

import arcpy

feature_class = r'C:\database.gdb\points' #Change to match your data
textfield='VEGNUMMER' #Change to match your data
checkfield='IntegerField' #Change to match your data

arcpy.MakeFeatureLayer_management(in_features=feature_class, out_layer='lyr')

oidlist=[]
with arcpy.da.SearchCursor('lyr',['OID@',textfield,checkfield]) as cursor:
    for row in cursor:
        templist=row[1].replace(" ","").split(',')
        if str(row[2]) in templist:
            oidlist.append(row[0])
sql="{0} IN({1})".format(arcpy.AddFieldDelimiters('lyr','OBJECTID'),", ".join(map(str,oidlist)))
arcpy.SelectLayerByAttribute_management(in_layer_or_view='lyr', 
                                       where_clause=sql)
#Add more code here to continue to work with the selection

Answer 2

If you are wanting to select by attribute then you can just treat the numbers as text in a string and search using wildcards. You can read about constructing SQL queries in arc here Say you were looking for number 6 you would enter something like this into the select by attribute dialog box:

"VEGNUMMER" LIKE '6' OR
"VEGNUMMER" LIKE '% 6' OR
"VEGNUMMER" LIKE '% 6, %'

The first line selects for only 6 the second line selects where 6 is the last number in the list, and the final line selects when 6 is in the middle.

If this is a one off then this should be a reasonable solution. If your shapefile is very large you'll probably be better off with a numeric solution, but that would require a different approach from the beginning.