topic Re: Using Special Characters in a "Where" Clause in Python Questions

Using Special Characters in a "Where" Clause

JoeBorgione — Sun, 12 Dec 2021 04:39:31 GMT

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?

Re: Using Special Characters in a "Where" Clause

DanPatterson_Retired — Sun, 12 Dec 2021 04:39:33 GMT

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']‍‍‍‍‍‍‍‍‍‍‍‍

Re: Using Special Characters in a "Where" Clause

JoeBorgione — Tue, 19 Dec 2017 11:34:52 GMT

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‌

Re: Using Special Characters in a "Where" Clause

JoshuaBixby — Tue, 19 Dec 2017 15:34:42 GMT

There are some good ideas in this thread: https://stackoverflow.com/questions/19970532/how-to-check-a-string-for-a-special-character

Re: Using Special Characters in a "Where" Clause

JoeBorgione — Tue, 19 Dec 2017 16:49:25 GMT

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‌