topic Re: select by attributes using like in Python Questions

select by attributes using like

ColinLang1 — Fri, 22 Sep 2017 17:27:55 GMT

I am trying to do a Select By Attributes where I have a field in one street file that has a column "POLICE" that lists the police department like this example: 'Sussex Police' and a file from another source that I am joining that has a column "Police" that lists the police department like this example: 'SUSSEX_POLICE'. For some names, there may be multiple underscore characters. I can obviously use UPPER() on the first field to make them almost match, and on the off chance that the data isn't consistent, I'm actually using UPPER on both sides. I was hoping I could use LIKE to get the underscore vs. the space, but it's just not matching. So this is my current expression:

where = 'UPPER("EmergencyServiceZones.POLICE") LIKE UPPER("msag_temp.Police")'

arcpy.SelectLayerByAttribute_management (road_layer, "NEW_SELECTION", where)

It returns nothing as it is.

Does anyone have any suggestions to adjust the query expression to find the matches?

Re: select by attributes using like

MitchHolley1 — Fri, 22 Sep 2017 17:46:32 GMT

Did you try:

where = 'UPPER("EmergencyServiceZones.POLICE") = UPPER("msag_temp.Police")' ?

Re: select by attributes using like

ColinLang1 — Fri, 22 Sep 2017 18:11:47 GMT

They're definitely not equal. Like was my only hope. I have found the solution, and it's a head-scratcher....

this fails: where = 'UPPER("EmergencyServiceZones.POLICE") LIKE UPPER("msag_temp.Police")'

this works: where = 'UPPER("msag_temp.Police") LIKE UPPER("EmergencyServiceZones.POLICE")'

for some bizarre reason, a space is LIKE an underscore, but an underscore is not LIKE a space.

Re: select by attributes using like

JoshuaBixby — Fri, 22 Sep 2017 22:42:34 GMT

You didn't state what the back-end data store is, but an underscore character is commonly a single-character wildcard in SQL LIKE operators. Since you didn't escape the underscore, you are effectively inserting a wildcard into your search, which affects the results.

Re: select by attributes using like

ColinLang1 — Mon, 25 Sep 2017 13:05:26 GMT

We're using SDE. and I'm not comparing a string against a field, I'm comparing one field against another, so I couldn't escape the underscores - they're in the source data.

Re: select by attributes using like

JoshuaBixby — Mon, 25 Sep 2017 16:03:01 GMT

You can handle the underscores just like you can handle the case differences, i.e., by either calling another SQL function or calling an existing one differently. For example with SQL Server (and maybe other DMBSs), LIKE has an escape_character argument that can be used. Since you are already calling UPPER, you aren't really comparing the source data any longer but modified copies of the source data ("SUSSEX POLICE" instead of "Sussex Police." A