Arcgis Pro - How to use "Like" function in definition query?

[AliForrester]

I used the "Like" function all the time in ArcMAP definition queries.  ArcGIS Pro doesn't seem to offer this function.  It is not shown as an option in clause mode for a definition query, and if i try to type it in manually in SQL mode it gives me an error.  I use the same syntax as in ArcMap so i'm not sure why its not accepted.  For example - if i want to filter out features based on a field that begins with 114 i would use Field Like '114%' (subbing in the name of the field of course). Pro does not accept this and tells me there is an error.  Why is this not working, and WHY is the Like function not offered in Clause Mode (contains, begins with, etc)??  Please help.

[RichardFairhurst]

What version of ArcGIS Pro are you using?  At version 1.1 the clauses that use LIKE in SQL mode are Begins With ('114%'), Ends With ('%114') and contains the text ('%114%').  The field has to be a text field to show these options. 

If I type the equivalent syntax for one of the Clause types in SQL mode it shows the correct Clause for each option when I switch back to Clause mode.  If I put in the expression Field LIKE '%1%14' in SQL mode the expression is accepted, but I cannot switch back to Clause mode, and it gives a message that "The expression cannot be edited in Clause mode".

I did not try this at version 1.0, so it may not have been available in that release (although I have no idea why it wouldn't have been included if it was missing)..

[AliForrester]

The field I'm trying to query is type "long" not "text".  The field type was not an issue in arcmap when using the Like function.  You're saying that it can only be used on "text" fields in Pro???  Well that's frustrating.  Thanks for the info.

[RichardFairhurst · Accepted solution]

The field type was always an issue as far as I know in ArcMap.  Like has never never been officially supported with a long field in any recognized SQL I know of, and Esri tries to comply with recognized SQL standards in general.  If it did not throw an error before then the Esri implementation was flawed and would not be portable outside of ArcMap.  I have always had to cast the long field to a text field to use any string comparisons such as the LIKE operator.  Even though ArcMap 10.3 includes LIKE in the query builder, it throws an error when I actually try to use Like in combination with a Long field, so it does not work in ArcMap and never should have worked.

For a File geodatabase you would use this syntax to cast your long field to a string (making the number of characters fit your expected long values):

CAST(Long_Field AS CHARACTER(50)) LIKE '114%'

SQL like this cannot be converted to the Clause mode.

If you want to convert a text field with numbers to a numeric value so you can use mathematical expressions or numeric comparisons you would cast the value to a Float:

CAST(Text_Field AS FLOAT) <= Long_Field

This SQL could not be converted to Clause mode either.

[AliForrester]

Thank you for the work around.  I actually tried the SQL expression on the "long" field in ArcMap 10.3 and it worked.  I copied and pasted it into ArcGIS Pro and got the error message.  Not sure why it worked in ArcMap and not Pro if they have the same field type rules.  In any case, thank you for your help with this.

[RichardFairhurst]

What database are you using?  If it is Access then I would expect funky SQL behaviors. 

File geodatabases and SDE is all I will use for actual work.  Like with a Long field does not work with a File Geodatabase.  I just tried it in ArcMap 10.3 and got an error.

The explicit cast syntax I provided is not a work around.   It is the proper SQL syntax for what you want to do.  Like is always described as a String operator and throwing an error is the proper implementation when Like is used directly with a Long field.  An implicit cast is not supposed to occur, and if it does occur for you the program is effectively inserting the cast I wrote out behind the scenes for you, which will never be a consistent behavior.  The behavior you desire is the work around to a standard SQL implementation.