SQL expression for finding strings including letters in ArcGIS Desktop?

Question

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

Answer 1

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.

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

Answer 3

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.

Answer 4

If your data is on MS-SQL try ranges within search exressions like

... where col like '%[a-z]%'

or a number afer a string with any one character between:

`... where col like '%[a-z]_[0-9]%'

If you need to look for a '-' put it at the beginning of the search string like

... where col like '%[-a-z0-9]%'

would match '-' or a letter or a number