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

AaronCraig2 — Fri, 09 Aug 2019 21:54:36 GMT

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.

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

JoshuaBixby — Sat, 10 Aug 2019 02:29:28 GMT

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.

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

AaronCraig2 — Mon, 12 Aug 2019 17:27:42 GMT

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.

topic Select by attribute clause builder is missing is LIKE, SQL mode missing field name list in ArcGIS Pro Questions

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

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

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