Using Special Characters in a "Where" Clause

403
4
Jump to solution
12-18-2017 02:56 PM
Highlighted
MVP Esteemed Contributor

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?

Reply
0 Kudos
1 Solution

Accepted Solutions
Highlighted
MVP Esteemed Contributor

I hate puzzles during marking.... you will have more test cases, report on ones that don't work... using lists right now to test.

punct = list(string.punctuation)

a = ['CHERRY SPRINGS?', 'A-A', 'A_ ', 'A!"#$%&\'()*+,-./:;<=>?@[\\]^_`{|}~']

out = []
for name in a:
    name = name.strip()
    val = [i for i in name if i not in punct]
    out.append("".join(val))

out
['CHERRY SPRINGS', 'AA', 'A', 'A']

View solution in original post

4 Replies
Highlighted
MVP Esteemed Contributor

I hate puzzles during marking.... you will have more test cases, report on ones that don't work... using lists right now to test.

punct = list(string.punctuation)

a = ['CHERRY SPRINGS?', 'A-A', 'A_ ', 'A!"#$%&\'()*+,-./:;<=>?@[\\]^_`{|}~']

out = []
for name in a:
    name = name.strip()
    val = [i for i in name if i not in punct]
    out.append("".join(val))

out
['CHERRY SPRINGS', 'AA', 'A', 'A']

View solution in original post

Highlighted
MVP Esteemed Contributor

I'll take "Things that make your eyes slam open at 4:00 am for $100..." 

Realizing I mixed metaphors with the '{}%'.format thing (it's IN, not LIKE!), I scrambled for my tablet just find you'd beaten me to it.  I'll give it a go once I get back to work.  Patterson to the rescue.  Again.

Thanks!

Dan_Patterson

Highlighted
MVP Esteemed Contributor
Highlighted
MVP Esteemed Contributor

Thanks Joshua- the string module is very cool, and until now unknown to me.  For a lot of the work I do it will come in very handy.

bixb0012

Dan_Patterson

Reply
0 Kudos