select by attributes using like

452
5
Jump to solution
09-22-2017 10:27 AM
ColinLang1
New Contributor III

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?

0 Kudos
1 Solution

Accepted Solutions
ColinLang1
New Contributor III

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.

View solution in original post

0 Kudos
5 Replies
MitchHolley1
MVP Regular Contributor

Did you try: 

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

0 Kudos
ColinLang1
New Contributor III

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.

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

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.

0 Kudos
ColinLang1
New Contributor III

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.  

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

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

0 Kudos