Using function to query text field in ArcGIS Select By Attributes?

Question

I can do this:

enter image description here

And get result:

enter image description here

On the other hand, when I do this:

enter image description here

I get an error message:

enter image description here

Why query using function is working on numeric field and is not working on text in both shapefile and file geodatabase?

• Midavalo: it doesn't appear to like the " marks around your field name. Works fine on a gdb layer, but not a shapefile with the quote marks, possibly because LEFT also works on text e.g. "Region"
• Midavalo: try using "Region" LIKE 'Auc%' instead
• Vince: Query parsing is different for every different data source, so stating the data source is critical
• Richard Fairhurst: What kind of data source is this? For a File Geodatabases you could use - SUBSTRING(REGION FROM 1 FOR 3)
• FelixIP: @Midavalo yes I know about LIKE. The reason I am asking is because I'd like to compare two fields. Functions ARE working on numeric fields and they are not working for string fields

Answer 1

For a File Geodatabases you could use -

SUBSTRING(REGION FROM 1 FOR 3) = SUBSTRING(REG FROM 1 FOR 3)

For a Shapefile use:

SUBSTRING(REGION, 1, 3) = SUBSTRING(REG, 1, 3)

I got the shapefile syntax from this Technical Article - HowTo: Compare a field with a substring in another field