AnsweredAssumed Answered

Using Special Characters in a "Where" Clause

Question asked by jborgion on Dec 18, 2017
Latest reply on Dec 19, 2017 by jborgion

I need to check for the presence of special characters in a given attribute field.  If I find them, I need to flag them as an error.  I've put all of them in a string  but I can't seem to get them to work in a "Where" clause the way I'd like them to:

specialcharacters = ("!","@","#","$","%","^","&","*","(",")","-","_","+","=","|","\\","}","]","{","[","\"","\'",":",";","?","/",">",".","<",",")


with arcpy.da.SearchCursor("MasterStreetNameFC_Lite","streetname") as cursor:
    for row in cursor:
        string = row[0]
        for i in string:
            if i in specialcharacters:
                select = "StreetName like '%{}'".format(i)
                arcpy.SelectLayerByAttribute_management("MasterStreetNameFC_Lite","ADD_TO_SELECTION",select)

In this example, I'd like to select just those records where the attribute StreetName ends in a special character.  Notice I have one record that meets that criteria:

 

However, when I run the cursor with the for loop, everything gets selected, even "LAURAL CANYON" which has no special characters.

 

The same thing happens regardless of what I use for the select variable: 

 

{}%'.format(i)           #  (starts with special character)
'%{}%'.format(i)         # (contains special character)
###etc

 

 

 

When I change the SelectByAttribute to a print, I get the results I'm looking for:

 

>>> with arcpy.da.SearchCursor("MasterStreetNameFC_Lite","streetname") as cursor:
     for row in cursor:
         string = row[0]
         for i in string:
             if i in specialcharacters:
                 print '{} {}'.format(row[0],i)
                
CHERRY SPRINGS? ?
"DEER SPRINGS "
DEER-SPRINGS -
ROCK_SPRINGS _

Where each of the 'misspelled' names are followed by thy offending special character. (The python highlighter is goofing on us with "DEER SPRINGS " showing it in green as a string: the reality is the " is just the special character that begins the name)

 

So I'm kind of stuck: can't make a selection so I can go in and fix them, but I can find them without a selection: how do I report the errors to the user?

Outcomes