Searching field with upper or lower case

3991
6
Jump to solution
11-05-2015 09:15 AM
CCWeedcontrol
Occasional Contributor III

I have a script that works good but only if the first letter of the input has the first letter upper case.

Currently every road name starts with an upper case letter. How can I format the expression to allow for either upper or lower case inputs.

 

Thanks.

import arcpy  
arcpy.AddMessage("Starting")  
pu = arcpy.GetParameterAsText(0)  
arcpy.AddMessage(pu)  
mxd = arcpy.mapping.MapDocument("CURRENT")  
df = arcpy.mapping.ListDataFrames(mxd, "Layers")[0]  
lyr = arcpy.mapping.ListLayers(mxd, "Roads", df)[0]

pu = arcpy.GetParameterAsText(0) 
arcpy.AddMessage(lyr.name)  
expression = "FENAME LIKE '%{}%'".format(pu) 
arcpy.AddMessage(expression)  
arcpy.SelectLayerByAttribute_management(lyr,"NEW_SELECTION",expression)  
df.extent = lyr.getSelectedExtent()  

arcpy.RefreshActiveView()  
arcpy.AddMessage("Completed")  
Tags (2)
0 Kudos
1 Solution

Accepted Solutions
DarrenWiens2
MVP Honored Contributor

It looks like ArcGIS SQL uses UPPER() rather than UCASE().

Try:

UPPER(FENAME) LIKE '%{}%'".format(pu.upper())

View solution in original post

6 Replies
DarrenWiens2
MVP Honored Contributor

Force both sides to uppercase, then compare (untested):

expression = "UCASE(FENAME) LIKE '%{}%'".format(pu.upper()) 

If pu = 'elm street', it would be turned to 'ELM STREET'. It would then match capitalized versions of records like 'Elm Street', 'ELM Street', 'eLm StReEt', etc., all of which would be treated like 'ELM STREET'.

0 Kudos
CCWeedcontrol
Occasional Contributor III

thanks for the reply.

getting error on expression = "UCASE(FENAME)LIKE '%{}%'".format(pu.upper())

Traceback (most recent call last):

  File "C:\GIS\Python Scripts\ZoomTo\ZoomToRoad.py", line 13, in <module>

    arcpy.SelectLayerByAttribute_management(lyr,"NEW_SELECTION",expression)

  File "c:\program files (x86)\arcgis\desktop10.2\arcpy\arcpy\management.py", line 6688, in SelectLayerByAttribute

    raise e

ExecuteError: ERROR 000358: Invalid expression

0 Kudos
DarrenWiens2
MVP Honored Contributor

It looks like ArcGIS SQL uses UPPER() rather than UCASE().

Try:

UPPER(FENAME) LIKE '%{}%'".format(pu.upper())
CCWeedcontrol
Occasional Contributor III

does the UPPER() only for the first word or for more then one word?

The feature class attributes are like the following Desert Hills Road,Desert Wolf Lane etc.

0 Kudos
CCWeedcontrol
Occasional Contributor III

Looks like it does. Thanks.

0 Kudos
DarrenWiens2
MVP Honored Contributor

UPPER() applies to the entire string within the parentheses: e.g. UPPER('Desert Hills') = 'DESERT HILLS'