How do I select by attribute to get the numbers from the attribute table list that has mixed strings/numbers ?

03-08-2016 10:21 AM
Frequent Contributor


I am not good at Select by Attributes but here I have a layer that has mixed strings/numbers  such as 1,2,3,...A,B.C,D,E , etc  They came from CAD layer I imported to File Geodatabase as a feature class.

I do know how to do simple query such as TextString = "A" to get all the A's only .

I am trying to select the numbers only from the attribute to export a new feature class.  I have to do the same with the lower capitals.

I could have open the attribute and highlighted them but it is time-consuming....

How do I do that ?

Here is the picture sample from the layer.


0 Kudos
5 Replies
Frequent Contributor III

There are a number of possible options.  I have done a lot of work with AutoCAD files.

Do you have a range of numbers or are the all single digit numbers?

if they are single digit simply do a search by attribute for TextString  = '1' or TextString = '2' etc.

Or take your file and export the table to a text file.  Select the TextString column and press the Sort A to Z button.

Excel will sort your data by Integers first, then entries that start with  a decimal then by those that are a string but start with a number and then by those that start with a Character.  To be more technical about it Excel will sort by pure numbers first and then by lowest ASCII code value.  i.e. ALT 034  is "  or ALT 033 is ! so it would sort the ! first.

All that aside.  Now you can delete all the other columns in your Excel table other than the one with the numbers.

Below is an example of what I did in Excel.  Pure numbers are sorted first then lowest ASCII character.

Now simply Delete all the rows in the table that are larger than a number.  Save your table.  Bring it back into ArcGIS and join the NumberColumn to the TextString keeping only matching numbers.  Export the joined Table as a new feature class.

IF your lower case characters are only single characters.  IF your feature with the TextString is a ShapeFile you can add a through z to the bottom of your number column in Excel and it should result in a case sensitive result.

1001   15
0 Kudos
MVP Honored Contributor

If you're open to creating a new field and populating it for your selection, you can do (Python parser):

solution stolen mostly from here:

MVP Emeritus

everyone should read the thread that Darren points to... there are some gems and cases in there that are really interested and it also exemplifies the general case that a great solution may still not apply to all cases and it really depends upon the nature of the data you have.  Fortunately the tabular structure of the data used within a GIS limits the possibilities and makes some of the choices simpler.

Worth a read... and a cavaet that the rating number has no reflection on how good the answer was but the debate around it.  Darren's posted solution will work nicely

0 Kudos
Regular Contributor III

If your data is stored in an sql database you could use PATINDEX('%[0-9]%', yourfieldhere ) > 0

This doesn't work for file geodatabases, or shapefiles I haven't tested on personal geodatabases.

Frequent Contributor

I appreciate for all your input and I haven't tried this yet but I am sure I will remember this.

0 Kudos