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

[AdrianWelsh]

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?

[RandyBurton]

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.

[AdrianWelsh]

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?)

[RandyBurton · Accepted solution]

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.

[JoshuaBixby]

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.

[RandyBurton]

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!

[JoshuaBixby]

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? 

[AdrianWelsh]

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

[AdrianWelsh]

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........)

[JoshuaBixby]

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