select by attributes using like

[ColinLang1]

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?

[MitchHolley1]

Did you try: 

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

[ColinLang1 · Accepted solution]

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.

[JoshuaBixby]

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.

[ColinLang1]

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.  

[JoshuaBixby]

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