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.