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

[PROBERT68]

Hi,

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.

[RobertBorchert]

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.

NumberColumn
0.1002
1004
1005
9999999
!<1009_16
"100
.1002_17
1001   15

[DarrenWiens2]

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: http://stackoverflow.com/questions/354038/how-do-i-check-if-a-string-is-a-number-float-in-python

[DanPatterson_Retired]

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

[WesMiller]

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.

[PROBERT68]

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