String field that contains numbers not selecting specific values

[melisahansen]

Hello, 

I have a string field that contains both full text values, all numeric values as well as both text and numeric together. Field name = TESTNAME- Examples ( "ADAM", "500", 500-ADAM")

I am trying to find all numeric values in this string field with a value less than a specific value, however it does not work properly. I've tried to CAST the field to INT, but that's not working either as I get a nvarchar error. The closest I've been able to come is getting LEN(TESTNAME)<5 AND TESTNAME < 4000, However, this does not return all appropriate values. For example, the value 600 or 500 will be ignored and not returned. Any suggestions?  Thanks in advance! 

[LanceCole]

Can you add an additional field to the table or feature?  If so, you could use field calculator to extract the number from the string using python. Then use that field for your select.

The following would return the number only (as a string) or an empty string for values with out a number.

''.join(c for c in !TESTNAME! if c.isdigit())

Your same string of 'ADAM' would return an empty string of '' and 500-ADAM would return '500'.  You can also use a code block to return an integer but you need to check to see if an empty string is returned first or you will get an error.  This also assumes there is only one number per string. A sting such as '12-ADAM14' would return '1214'. Below is a code block you could use in field calculator to return a number rather than a string.

def returnNum(testname):
  tmp = ''.join(c for c in testname if c.isdigit())
  if tmp != '':
    return int(tmp)
  else:
    return 0

This would be called with: 

returnNum( !TESTNAME!)

[melisahansen]

I need to be able to do this with just a select by attributes, not by creating a new field. Any ideas with that limitation? 

[LanceCole]

I had the feeling you did not want to add additional fields.  What version of ArcGIS are you using and what level of licensing?  What is the data source, i.e. shapefile, GDB, Enterprise DB, etc.?  Select by Attribute only has a limited subset of SQL functions but there are other tools that may work or there is the option to script a select.  Are these acceptable options?

[melisahansen]

Yes  that should be an option, I'm using 10.8.1. Its a FC in SDE. 

[JoshuaBixby]

When it comes to SQL support, the data source is a big factor in what can and can't be done.  As LanceCole asked, what is the data source?

[melisahansen]

SDE FC

[JoshuaBixby]

EGDBs can be deployed on a variety of DBMS, so it is good to include which one since different DBMS implement SQL support differently.

[LanceCole]

Since you are on SDE you have available a large range of SQL commands.  

Try the following for the WHERE clause in your Select by Attribute.  This is not mine and is available from the blog Extracting numbers with SQL Server. The 50 in the statement is just to limit the length of the string being processed.  If your field is larger than 50 characters, set it to the max size of the field you are checking against or you can replace it with LEN(TESTNAME). However, the statement is cumbersome already.  Please replace "TESTNAME" in all four (4) locations with the field you are using.  The "BETWEEN 1 AND 4000" is from your example of "< 4000" and can be changed to what ever you need.  Setting it to "= 0" will return records without numbers.

(SELECT LEFT(SUBSTRING(TESTNAME, PATINDEX('%[0-9]%', TESTNAME), 50), PATINDEX('%[^0-9]%', 
SUBSTRING(TESTNAME, PATINDEX('%[0-9]%', TESTNAME), 50) + 'X') -1)) BETWEEN 1 AND 4000

One of the reasons it is so complex is is looks for a number anywhere in the string.  Also, It will only find the first number in a string.  "500-ADAM-600" will only be selected for 500 and not 600.  Tested on a MS_SQL SDE database using ArcGIS 10.8.1 Select by Attribute.