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?

  • 1
    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! :)
    – Hornbydd
    Commented Oct 7, 2012 at 22:40
  • 2
    I really wish Esri would (could?) standardize SQL syntax for all of the different data formats.
    – nmpeterson
    Commented Dec 4, 2013 at 19:09

4 Answers 4


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

  • 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
    Commented Oct 6, 2012 at 17:44
  • 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.
    – Rayner
    Commented Oct 6, 2012 at 17:49
  • 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) Commented Oct 8, 2012 at 18:18

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.

  • 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.
    – Rayner
    Commented Oct 8, 2012 at 11:07
  • 4
    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.
    – user22369
    Commented Sep 26, 2013 at 19:22

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

  • The query doesn't work in ArcMap 10.1 against a file geodatabase.
    – Rayner
    Commented Oct 6, 2012 at 17:37

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.