7

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 Oct 7 '12 at 22:40
  • 1
    I really wish Esri would (could?) standardize SQL syntax for all of the different data formats. – nmpeterson Dec 4 '13 at 19:09
10

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 Oct 6 '12 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 Oct 6 '12 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) – RyanDalton Oct 8 '12 at 18:18
7

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 Oct 8 '12 at 11:07
  • 2
    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 Sep 26 '13 at 19:22
2

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 Oct 6 '12 at 17:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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