topic Re: How do I select by attribute to get the numbers from the attribute table list that has mixed strings/numbers ? in Data Management Questions

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

DEAF_PROBERT_68 — Tue, 08 Mar 2016 18:21:04 GMT

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.

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

RobertBorchert — Mon, 14 Mar 2016 20:08:06 GMT

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

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

DarrenWiens2 — Mon, 14 Mar 2016 20:22:45 GMT

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

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

DanPatterson_Retired — Mon, 14 Mar 2016 22:10:34 GMT

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

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

WesMiller — Tue, 15 Mar 2016 15:17:31 GMT

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.

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

DEAF_PROBERT_68 — Wed, 23 Mar 2016 00:49:03 GMT

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