Using LOWER() in arcpy select by attribute

Question

I just want to include lower() in my query to make it irrespective of case.

When I use this I get an error:

arcpy.SelectLayerByAttribute_management("BurrRidgeTreeInventoryMaster""NEW_SELECTION",' lower("species") like 'maple%'')

And when I use this I get everything selected, not just trees that contain 'maple%'

arcpy.SelectLayerByAttribute_management("BurrRidgeTreeInventoryMaster","NEW_SELECTION"," lower('species') like 'maple%'")

What do I have wrong?

• Midavalo: Try leaving off the quote marks around your field name e.g. """ lower(species) like 'maple%' """
• Midavalo: That said, a SQL query ignores case, so it shouldn't make any difference
• nsm91: actually when I leave out the lower the query is successful but it returns 0 entries as opposed to hundreds with lower

Answer 1

Try leaving off the quote marks around your field name e.g. """ lower(species) like 'maple%' """

arcpy.SelectLayerByAttribute_management("BurrRidgeTreeInventoryMaster","NEW_SELECTION",""" lower(species) like 'maple%' """)

The triple quotes around the query """ this like 'that' """ enables you to include different quote marks in python as required without it breaking your code.

The Where Clause in a arcpy.SelectLayerByAttribute_management() is a SQL query and case should be irrelevant here, so I'm surprised it makes any difference.