8

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

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

  • 3
    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
2

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.

2

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

  • 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
2

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.

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.