10

For a standard LIKE query against an Oracle-based ArcSDE feature class, the underscore character represents a single character wildcard when used with a string.

I'm trying to impose a definition query to find a text string that starts with 4 digits exactly followed by an underscore character.

Does anyone know how I would specify an underscore character itself in a query or if/what the escape character might be?

MDHald's answer works for file geodatabases but my case is specific to Oracle. Falsely assumed that ArcSDE and file geodatabase query would function the same for this case.

  • Escape character is usually a backslash \ - I believe this is also the case with Oracle, so you'd want to look for \_ if searching for the underscore. – Midavalo Feb 25 '16 at 21:31
  • @Midavalo, that was the first thing that came to mind. My query was CABLE = '_____\_%' , which returned zero results. – Eok Feb 25 '16 at 21:35
  • you might need to use LIKE (although you do mention LIKE in your question) - CABLE LIKE '____\_%'. I'll have a play here although I'm using SQL Server rather than Oracle so may get different results – Midavalo Feb 25 '16 at 21:37
  • 1
    Try CABLE LIKE '____\_%' ESCAPE '\' - from SQL reference for query expressions used in ArcGIS - Desktop Help – Midavalo Feb 25 '16 at 21:46
  • 1
    @Midavalo, found the exact same thing right after you did – Eok Feb 25 '16 at 21:51
7
+100

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 '$'

3

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.

  • Your answer works for file geodatabases, but I didn't realize that the underlying DBMS would be so picky. Oracle doesn't like the query. – Eok Feb 25 '16 at 21:20
  • Oracle gets a bit tricky with [DATABASE]..[TABLENAME] it requires those double points. If the query doesn't work as a definition you could always create a view (right click in your database>select New>select View>) in your SDE(Presuming you have one setup if you are pulling from Oracle) and then write out a similar query. – MDHald Feb 25 '16 at 21:25
3

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 :-)
>>> 
  • 1
    Thanks for that...waste so much time on "special" character workarounds and syntax.... hopefully I remember where to find it next time it comes up. – Mike Jun 17 '16 at 23:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.