Using String Functions (LEFT, RIGHT, MID) in Select By Attributes against a File Geodatabase using ArcMap 10

Question

I finally (albeit reluctantly) made the switch from a personal geodatabase (pGDB) to a file geodatabse (fGDB) not too long ago. The main reason it took so long to switch has to do with how Select by Attributes deals with string functions.

For example, I used to be able to easily select all records where the first character of a particular field starts with "A".

left([Some_Field],1 = "A")

This works without a hitch when querying against a pGDB. However, try to do the same thing against a fGDB.

left("Some_Field",1 = "A")

This returns the following error:

There was a problem selecting
An invalid SQL statement was used.

I realize I can use the field calculator to calculate a new field equal to the first character of some other field. But who wants to do that every time?

Is there a way to use string functions inside Select By Attributes?

• Hornbydd: Rayner, I still use pgdb for storing output I want to analyse in another application such as minitab as you can use odbc to suck data out of the pdgb, you still can't do that with a fgdb. Don't give up on them yet! :)
• nmpeterson: I really wish Esri would (could?) standardize SQL syntax for all of the different data formats.

Answer 1

Your queries should follow the SQL format discussed here. For what you're looking for, you can use something like this:

"Some_Field" LIKE 'A%'

The % is a wild card, so this would return records that have values in "Some_Field" that start with 'A'.

• Rayner: This works. For some reason in the back of my mind I stayed away from LIKE . . . maybe this comes from the old 8.x days when it was a bit more unreliable than it is now.
• Rayner: Likewise, '%A' selects records that end in 'A' You can use '%A%' but that selects all records that have an 'A' anywhere in the field. The only thing that really remains unsolved is the MID function. I used to query mid([FacilityID],2,4) <> [TaxMapCalc] against a pGDB but it doesn't work against a fGDB.
• RyanKDalton: It might not be as easy, but you can also use the sql wildcard "underscore" which represents only a single character. So your mid query could be something like: "Some_Field" LIKE '_ _A%' (but with no spaces)

Answer 2

You can use Substring("fieldname", start, length) to mimick, left(), right() and mid() functions when querying a fgdb.

Left and Mid are obvious.

To extract the right 2 characters would be Substring("fieldname", char_length("fieldname") - 1), 2).

Column numbers are 1-based.

• Rayner: I see where it shows Substring here: resources.arcgis.com/en/help/main/10.1/index.html#//… but I'm at a loss as how to use this inside the ArcMap Select By Attributes window.
• user22369: SUBSTRING("Field" FROM 1 FOR 2) in ('AA', 'AR') Will return all records where the field attributes start with 'AA' OR 'AR'. Same as "Field" LIKE 'AA%' OR "Field" LIKE 'AR%' But nice when you don't want to write a bunch of OR's for a long list.

Answer 3

Try this code:

left("Some_Field",1) = 'A'

where the comparison operator is outside the parentheses, and the string value is in single quotes.

Note that string searches are case sensitive in a file geodb. So you might also have to use the UPPER or LOWER functions, or search for 'A' or 'a'.

• Rayner: The query doesn't work in ArcMap 10.1 against a file geodatabase.

Answer 4

For anyone landing here searching for a reason why the left() function doesn't work in arcgis, this is what I found:

LEFT() and RIGHT() don't work for fgdb's. You must use SUBSTRING(field FROM start for NumChars)

The correct syntax for the SUBSTRING function is:

SUBSTRING("Field" FROM 1 FOR 3) - this example returns everything from the first char to the third char (if the field value was Apple, this would return App).