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'm working on ArcGIS 9.3.1. I have a shapefile with a string field containing point names like these:

10
12
12/13
t/9
6577y
1234
12t9
124y67
u9

and so on (and yes, I know it looks like a colossal mess. It's not my design). Using WHERE MyColumn LIKE '%Search String%' I can find (using select by attribute) the ones containing a specific letter or a backslash (/).

MY QUESTION IS THIS:

How can I select all values containing a letter (in this example: t/9, 6577y, 12t9, 124y67, u9), not caring what letter it is or it's position? (is there a way to specify a position?)

share|improve this question
2  
You sure you didn't just slam your head on your keyboard and pop that out? :P –  blah238 Aug 22 '11 at 6:29
1  
unfortunately, NO. –  jonatr Aug 22 '11 at 9:42

4 Answers 4

For something quick and (very) dirty, you could use something like this to find all the records with any of the standard 26-letter alphabet characters. Use find and replace in your favorite text editor to change the MyColumn name to your real column name.

UPPER("MyColumn") LIKE('%A%') OR
UPPER("MyColumn") LIKE('%B%') OR
UPPER("MyColumn") LIKE('%C%') OR
UPPER("MyColumn") LIKE('%D%') OR
UPPER("MyColumn") LIKE('%E%') OR
UPPER("MyColumn") LIKE('%F%') OR
UPPER("MyColumn") LIKE('%G%') OR
UPPER("MyColumn") LIKE('%H%') OR
UPPER("MyColumn") LIKE('%I%') OR
UPPER("MyColumn") LIKE('%J%') OR
UPPER("MyColumn") LIKE('%K%') OR
UPPER("MyColumn") LIKE('%L%') OR
UPPER("MyColumn") LIKE('%M%') OR
UPPER("MyColumn") LIKE('%N%') OR
UPPER("MyColumn") LIKE('%O%') OR
UPPER("MyColumn") LIKE('%P%') OR
UPPER("MyColumn") LIKE('%Q%') OR
UPPER("MyColumn") LIKE('%R%') OR
UPPER("MyColumn") LIKE('%S%') OR
UPPER("MyColumn") LIKE('%T%') OR
UPPER("MyColumn") LIKE('%U%') OR
UPPER("MyColumn") LIKE('%V%') OR
UPPER("MyColumn") LIKE('%W%') OR
UPPER("MyColumn") LIKE('%X%') OR
UPPER("MyColumn") LIKE('%Y%') OR
UPPER("MyColumn") LIKE('%Z%')

For anything more complex I would instead use regular expressions in a SearchCursor in Python (see the re module), or put the data in a real database and use its specific string searching capabilities.

share|improve this answer

A workaround would be to add a field which is easy to query using the Python parser and the isdigit string method. Here is an example

>>> "1234".isdigit()
True
>>> "123a".isdigit()
False
>>>

Then query for the the True or False condition you need. If you want to know the position of the first non-numeric character, add a numeric field, then use this python code block (tested arcmap 10, sorry don't have 9.3)

def locater(aval):
  position = -1
  for i in aval:
    isnum = i.isdigit()
    if not isnum:
      position = aval.index(i)
  return position

called using this in the expression line locater(!YourTextFieldNameHere!)

it will return the position within the string or -1 for all numeric.

share|improve this answer

You might consider adding an integer column to your shapefile, calling it "MATCH". Then use the field calculator to perform string matching tests in a pre-logic code block. After the Match field is populated, you may then select on it using the Select by attributes tool.

Side note: It's been a long time but it seemed like INFO supported a pseudo column (was it $RECSEL ?) that allowed you to select things using a CALCULATE command. This use case points out a situation where something like that would be helpful. IMO, Esri should enhance the field calculator to support pseudo columns for selection. The field calculator would present a boolean target field when the user right clicks on the unlabeled block next to the left most column's field header.

Update: If you agree, please vote for my idea.

enter image description here

share|improve this answer
    
Can you specify what you mean by "string matching tests in a pre-logic code block" ??? –  jonatr Aug 22 '11 at 22:08
    
In the field calculator click the "show codeblock" checkbox. You should then be able to use VBA string manipulation functions. Don't get too carried away though, VBA goes away at 10.0. See this KB article. –  Kirk Kuykendall Aug 22 '11 at 22:21

convert to a personal geodatabase.

wildcards:

question mark = any alpha character pound sign = any numeric character

[pointname] like "12?" should return 12A, 12b, 12c and so on.

share|improve this answer

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.