Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?

share|improve this question
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

4 Answers 4

up vote 3 down vote accepted

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

share|improve this answer
    
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

Split Capitalize the first letter of a text or string field in ArcGIS Desktop

share|improve this answer

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.

share|improve this answer
    
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

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

share|improve this answer
    
The query doesn't work in ArcMap 10.1 against a file geodatabase. –  Rayner Oct 6 '12 at 17:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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