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

151
2
08-09-2019 02:54 PM
Highlighted
Occasional Contributor

Hi,

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. 

Reply
0 Kudos
2 Replies
Highlighted
MVP Esteemed Contributor

If the primary criteria is string/text length, using CHAR_LENGTH is both more efficient and straightforward.  With ArcGIS Pro, the SQL editing allows for auto-complete functionality, which I find more efficient than selecting operators and fields from dialog windows.

Highlighted
Occasional Contributor

Heh, there are a million ways to slice the Char Length pie. I actually prefer to use FME to do such things on mass. The point I am trying to make is there is no way to use clause mode, with LIKE, without leading or trailing (or both) wildcards.

Reply
0 Kudos