topic Using SQL to "Select by Attributes" comparing fields... in Geodatabase Questions

Using SQL to "Select by Attributes" comparing fields...

AdrianWelsh — Mon, 28 Jan 2019 17:00:52 GMT

I'm trying to write a simple SQL query that just isn't working. I have two fields in my file geodatabase layer (ArcGIS Pro 2.3) and I want to select the records that match my simple SQL query of:

written out to LEFT(Layer,3) = OWNERSHIP_NUMBER

But I get an error saying that "The SQL expression has invalid syntax".

I must be doing it wrong somewhere but I don't know where. These are both FIELDS and not RECORDS, so that likely makes a difference in how my syntax is supposed to be written.

Looking up this article: SQL reference for query expressions used in ArcGIS—ArcGIS Pro | ArcGIS Desktop

it says "

String functions can be used to format strings. For instance, the LEFT function would return a certain number of characters starting on the left of the string. In this example, the query would return all states starting with the letter A:
LEFT(STATE_NAME,1) = 'A'

So, it makes me think my syntax is mostly right since both of my fields are strings. Any advice on making my SQL query syntax work?

Re: Using SQL to "Select by Attributes" comparing fields...

RandyBurton — Mon, 28 Jan 2019 17:24:01 GMT

My initial reaction is that it should be written as field = some value, therefore:

OWNERSHIP_NUMBER = LEFT(Layer,3)‍

This is assuming that ownership number is text, as Left is a text function. It might be helpful if you could share an example of ownership_number and layer, if you are still having problems. Hope this helps.

Re: Using SQL to "Select by Attributes" comparing fields...

AdrianWelsh — Mon, 28 Jan 2019 17:30:43 GMT

Hmmm, that gave me the same error.

Details: both fields are text fields and contain numbers.

ownership_number are normally 3 digit numbers that sometimes contain a letter. Examples are 100, 101, 102, 102B, etc.

Layer has the same three digit numbers with additional info usually in the case of colons. So, examples: 100:E 100:PUE, 101:ST, etc.

I just want this SQL query to work so I can modify it to narrow down my search, etc. (finding where these numbers do not match). Thanks for your help!

(Bonus, does it matter that one text field is 255 characters long and the other is 8000 characters?)

Re: Using SQL to "Select by Attributes" comparing fields...

RandyBurton — Mon, 28 Jan 2019 19:29:03 GMT

I think since you are using a file geodatabase you might need to use something like (you'll want to make sure both sides are a string of 3 characters):

SUBSTRING("OWNERSHIP_NUMBER" FROM 1 FOR 3) = SUBSTRING("Layer" FROM 1 FOR 3)‍

(This from an old thread: Definition query SQL won't accept Left() Function)

And according to SQL reference for query expressions used in ArcGIS

All SQL used by the file geodatabase is based on the SQL-92 standard.

Re: Using SQL to "Select by Attributes" comparing fields...

JoshuaBixby — Mon, 28 Jan 2019 20:03:47 GMT

Randy, I believe your comment is correct, at least in terms of making it work. Adrian's confusion originates from 2 facts: 1) "LEFT" is commonly implemented in DBMSs, and 2) Esri's own documentation clearly states "LEFT" should work.

Re: Using SQL to "Select by Attributes" comparing fields...

RandyBurton — Mon, 28 Jan 2019 20:24:17 GMT

At one time, I would swear that I used "LEFT" with a file geodatabase. But it wasn't working today ... which I attributed to just being a Monday!

Re: Using SQL to "Select by Attributes" comparing fields...

JoshuaBixby — Mon, 28 Jan 2019 21:30:32 GMT

I just tried in Pro 2.3, 10.7 Prerelease, and 10.3.1; and all of them generated the same invalid syntax error. Maybe 9.x?

Re: Using SQL to "Select by Attributes" comparing fields...

AdrianWelsh — Mon, 28 Jan 2019 21:44:07 GMT

Thanks for sharing that Randy. I'll have to check it out tomorrow to see if it will work. I'll report back here!

Re: Using SQL to "Select by Attributes" comparing fields...

AdrianWelsh — Tue, 29 Jan 2019 16:30:06 GMT

Randy,

Nice digging on that old thread. Your suggestion mostly worked! I don't know why it didn't all-the-way work but that is on my end.

(my example is, after the query of

SUBSTRING("Layer" FROM 1 FOR 3) <> SUBSTRING("OWNERSHIP_NUMBER" FROM 1 FOR 3)‍

wherever I have null values, these were not part of my selection - so, Layer would = 144B and OWNERSHIP_NUMBER would = <null>, and these are clearly different, this record was not selected. This is true when I flip-flop the query as well.).

Either way, this gets me past my initial question of why LEFT didn't work. Thanks again!

(maybe Esri should change that documentation........)

Re: Using SQL to "Select by Attributes" comparing fields...

JoshuaBixby — Tue, 29 Jan 2019 19:30:50 GMT

The presence of NULL introduces three-value logic into SQL comparison operators. What you are seeing regarding your comparisons with NULL is expected. It might be worth checking out Modern SQL: Three-Valued Logic (3VL) — Purpose, Benefits and Special Cases

Re: Using SQL to "Select by Attributes" comparing fields...

RandyBurton — Tue, 29 Jan 2019 20:15:29 GMT

And the page referenced also says:

A Lot Has Changed Since SQL-92

And so it has.

Re: Using SQL to "Select by Attributes" comparing fields...

AdrianWelsh — Sun, 12 Dec 2021 08:45:06 GMT

Geeze, you're right! This is a great segment from that article:

The result of each of the following comparisons is therefore unknown:0
NULL = 1 
NULL <> 1 
NULL > 1 
NULL = NULL
Nothing equals null. Not even null equals null because each null could be different.

Haha, oh null. What a conundrum