Querying for underscore character in ArcMap?
Managed to track down the answer.
You can specify an ESCAPE character in query such as:
MY_FIELD LIKE '____$_%' ESCAPE '$'
This will search for exactly 4 characters followed by an underscore character plus anything else after that.
Found the documentation on this page: http://desktop.arcgis.com/en/arcmap/10.3/map/working-with-layers/sql-reference-for-query-expressions-used-in-arcgis.htm
Not sure how far back or what versions this is valid for but it works for ArcGIS Desktop 10.3.
Excerpt from the documentation:
x [NOT] LIKE y [ESCAPE 'escape-character']
Use the LIKE operator (instead of the = operator) with wildcards to build a partial string search. The percent symbol (%) means that anything is acceptable in its place: one character, a hundred characters, or no character. Alternatively, if you want to search with a wildcard that represents one character, use an underscore (_). If you need to access noncharacter data, use the CAST function. For example, this query returns numbers that begin with 8 from the integer field SCORE_INT:
CAST ("SCORE_INT" AS VARCHAR) LIKE '8%'
To include the percent symbol or underscore in your search string, use the ESCAPE keyword to designate another character as the escape character, which in turn indicates that a real percent sign or underscore immediately follows. For example, this expression returns any string containing 10%, such as 10% DISCOUNT or A10%:
"AMOUNT" LIKE '%10$%%' ESCAPE '$'
You will need to employ CHAR_LENGTH and SUBSTRING in order for this to work. It would look as follows:
CHAR_LENGTH ("yourfieldname") =5 AND SUBSTRING("yourfieldname", 1, 4) <> '_'
where yourfieldname = the name of your field.
Do not delete the "" in the code though. Copy as is and replace only the text yourfieldname.
I came across this Q&A which helped me to solve why I was unable to use a where clause on an ArcPy search cursor which could restrict the cursor to just those records which contained an underscore (_
) in a particular text field.
By the time I found it I had already developed a code snippet to illustrate the problem so, rather than waste that effort, I have added the solution to it and am now posting it here to perhaps help a future visitor with the same problem.
The test uses a file geodatabase and was run at ArcGIS 10.2.2 for Desktop.
import arcpy
arcpy.CreateFileGDB_management(r"C:\Temp","test.gdb")
arcpy.CreateFeatureclass_management(r"C:\Temp\test.gdb","testFC")
arcpy.AddField_management(r"C:\Temp\test.gdb\testFC","testField","Text")
cursor = arcpy.da.InsertCursor(r"C:\Temp\test.gdb\testFC",["testField"])
cursor.insertRow(["ABCD"])
cursor.insertRow(["A_CD"])
cursor.insertRow(["XYZ"])
cursor.insertRow(["X_Z"])
del cursor
where_clause = "testField LIKE '%C%'"
print("Using where_clause of {0} to limit search cursor to print any values containing the letter C:".format(where_clause))
with arcpy.da.SearchCursor(r"C:\Temp\test.gdb\testFC",["testField"],where_clause) as cursor:
for row in cursor:
print(row[0])
print("This is the expected result :-)")
where_clause = "testField LIKE '%_%'"
print("\nUsing where_clause of {0} to limit search cursor to print any values containing an underscore (_):".format(where_clause))
with arcpy.da.SearchCursor(r"C:\Temp\test.gdb\testFC",["testField"],where_clause) as cursor:
for row in cursor:
print(row[0])
print("This is not what I was hoping for :-(")
where_clause = "testField LIKE '%$_%' ESCAPE '$'"
print("\nUsing where_clause of {0} to limit search cursor to print any values containing an underscore (_):".format(where_clause))
with arcpy.da.SearchCursor(r"C:\Temp\test.gdb\testFC",["testField"],where_clause) as cursor:
for row in cursor:
print(row[0])
print("This is what I was hoping for :-)")
The output is:
>>>
Using where_clause of testField LIKE '%C%' to limit search cursor to print any values containing the letter C:
ABCD
A_CD
This is the expected result :-)
Using where_clause of testField LIKE '%_%' to limit search cursor to print any values containing an underscore (_):
ABCD
A_CD
XYZ
X_Z
This is not what I was hoping for :-(
Using where_clause of testField LIKE '%$_%' ESCAPE '$' to limit search cursor to print any values containing an underscore (_):
A_CD
X_Z
This is what I was hoping for :-)
>>>