AnsweredAssumed Answered

Select by attribute clause builder is missing is LIKE, SQL mode missing field name list

Question asked by AaronCraig on Aug 9, 2019
Latest reply on Aug 12, 2019 by AaronCraig



Simple use case example: I am looking to find any postal codes in my dataset that are 7 characters long vs the standard 6 characters. I don't want to define a specific search for criteria (ie. looking for delimiters such as spaces, dashes, commas, etc.), rather I just want a count of the number of records that have more than 6 characters. 


(EDIT: I use FME to do this on mass normally, and don't need suggestions on the many ways to do such a thing, just using this as an example of how LIKE without wildcards is not possible in clause mode).


Normally I would simply look for the 7 char records using this SQL (which is works when using SQL mode): 

[Postal Code] LIKE '_______' (7 underscores)


In clause mode, this expression is impossible. There is no operator that uses LIKE without a % wildcard.


For example, using "contains the text" wraps the statement in percent signs:

Clause: Where [Postal Code] contains the text '_______'

SQL: [Postal Code] LIKE '%_______%'


Also, SQL mode needs an editor. A simple port over from ArcMap would suffice. If simple, ... maybe we should call them "Advanced" SQL statements (such as LIKE without %) need to be written in the SQL text box, give us at the very least a field list.


So just hoping ESRI is aware there is no way to build a LIKE statement in clause mode without % wildcards (which will produce very different results) and that SQL mode needs a field list for easy editing. Else we are left with more basic functionality lost by switching over to Pro.