nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

ArcGIS Pro: how to QUERY a text field with a numeric operator

ChuckBenton

I'm using an AGOL based feature layer that has all its features stored as text. I want to use a query such as "Tot_Value >= 500000", however I get an error since Tot_Value is a text string. I recall an earlier version of ArcMap allowed this type of operation using the same data source, so I believe it can be done. I'm not a SQL expert, but assume I can cast this to an Int or similar, however a web search is coming up dry, and attempts on my part generate errors. Suggestions?

Find more posts tagged with

Accepted answers

All comments

FredCoud

It worked perfectly fine for me, with ArcGIS Pro 2.1.3. Thanks !

TomasSmieszek1

Chuck,

There is not noticeable problem with the Layer Name but “PAAG_-_Feature_Layer” looks suspicious. My first step would be making it shorter and simpler.

ChuckBenton

Richard,

Thanks for the insights. This is coming from a shapefile that was used to create the feature layer I'm working with.

I have escalated this to an ESRI support request, still getting things sorted out.

Chuck

RichardFairhurst

Nowhere do you say what database you are using. The answer is very dependent on the database. Asking this question without ever revealing the database you are using makes this an impossible question to troubleshoot. If this is a file geodatabase then CAST(TOT_VALUE AS INTEGER) > 500000 is valid and works.

I just ran Select By Attribute on a text field using a Cast on a Substring in a file geodatabase with the expression

CAST(SUBSTRING(LINE_LINK FROM 2 FOR 12) AS INTEGER) > 7000000

I ran it and got back the expected result. Therefore all I know is that you are not using a file geodatabase. This may also work for a shapefile, but I did not test that.

File GDB Cast SQL File GDB Cast Selection Result

The same expression does not work for SQL Server. For that I have to use:

CAST(SUBSTRING(LINE_LINK,2,7) AS INT) > 7000000

SQL Server Cast Expression SQL Server Cast Selection Result

The answer will be different for ORACLE and may be different for a shapefile. You have to search online for the syntax that specifically applies to each database to use methods like CAST or SUBSTRING, since there is no standard SQL implementation for these methods that works across databases.

I have never used an AGOL Feature Layer, but you need to know which database that layer uses and find the syntax for it online to come up with a valid Cast expression.

QuinnBast

Hello there,

Firstly I want to point out, your original function threw an error because you are trying to compare integer to a string. This can be avoided in your case by using the following code:

Tot_Value >= '500000'‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

However, this may not be what you want either. The comparison checks each letter of the string one at a time until it finds a difference. However, this can cause problems if your values in that field are of varying length. See screenshot below to see what happens when comparing different length strings.

If you would like this solution to work, one possibly remedy is to ensure that all the values in your text field are the same length. However, if this is not possible we will need an alternate solution. We can make use of the string comparisons by using the SQL's LEFT and RIGHT operators. This allows us to extract the right-most side of the string for comparison. For example RIGHT('102', 2) will return '02'.

How is this useful?

If we want to make sure a number is greater than 5, we can use RIGHT(Tot_Value, 1) > '5'. However, notice that a value of '12' will return False as it will compare '1' > '5' first. Therefore, to remedy this we can filter out entries that are always larger by checking the string length, and then, only when an item is the same length as the search number, we check the value. Thus we can apply the following where clause:

LENGTH<SPAN class="punctuation token">(</SPAN>Tot_Value<SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">></SPAN> <SPAN class="number token">1</SPAN>‍ <SPAN class="operator token">OR</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="keyword token">RIGHT</SPAN><SPAN class="punctuation token">(</SPAN>Tot_Value<SPAN class="punctuation token">,</SPAN> <SPAN class="number token">1</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">></SPAN> <SPAN class="string token">'5'</SPAN>‍‍‍‍‍‍‍‍ <SPAN class="operator token">AND</SPAN> LENGTH<SPAN class="punctuation token">(</SPAN>Tot_Value<SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">=</SPAN> <SPAN class="number token">1</SPAN>‍<SPAN class="punctuation token">)</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

This will ensure that only numbers larger than '5' are obtained. Once the string hits the length threshold, that the minimum value (RIGHT) is always greater than '5'. To apply this for 500000 we would do this:

Now, this query may depend on your DBMS. For example, ORACLE does not have a RIGHT function, and thus I need to use SUBSTR instead. Make note of your DBMS and what type of string functions are available. That being said, this type of query should be possible in all SQL variants. That being said, let's apply this to your case of 500000:

To compare if a string is larger than a number:

<SPAN class="token function">LEN</SPAN><SPAN class="punctuation token">(</SPAN>Tot_Length<SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">>=</SPAN> <SPAN class="number token">6</SPAN> <SPAN class="operator token">OR</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="keyword token">RIGHT</SPAN><SPAN class="punctuation token">(</SPAN>Tot_Length<SPAN class="punctuation token">,</SPAN> <SPAN class="number token">6</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">></SPAN> <SPAN class="string token">'500000'</SPAN>‍‍‍‍‍‍ <SPAN class="operator token">AND</SPAN> <SPAN class="token function">LEN</SPAN><SPAN class="punctuation token">(</SPAN>Tot_Length<SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">=</SPAN> <SPAN class="number token">6</SPAN><SPAN class="punctuation token">)</SPAN>‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

For oracle:

LENGTH<SPAN class="punctuation token">(</SPAN>Tot_Length<SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">></SPAN> <SPAN class="number token">6</SPAN> <SPAN class="operator token">OR</SPAN> <SPAN class="punctuation token">(</SPAN>SUBSTR<SPAN class="punctuation token">(</SPAN>Tot_Length<SPAN class="punctuation token">,</SPAN> <SPAN class="operator token">-</SPAN><SPAN class="number token">6</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">6</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">></SPAN> <SPAN class="string token">'500000'</SPAN>‍‍‍‍‍‍‍ <SPAN class="operator token">AND</SPAN> LENGTH<SPAN class="punctuation token">(</SPAN>Tot_Length<SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">=</SPAN> <SPAN class="number token">6</SPAN><SPAN class="punctuation token">)</SPAN>‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

To compare if a string is smaller than a number:

<SPAN class="token function">LEN</SPAN><SPAN class="punctuation token">(</SPAN>Tot_Length<SPAN class="punctuation token">)</SPAN> <SPAN class="operator token"><</SPAN> <SPAN class="number token">6</SPAN> <SPAN class="operator token">OR</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="keyword token">RIGHT</SPAN><SPAN class="punctuation token">(</SPAN>Tot_Length<SPAN class="punctuation token">,</SPAN> <SPAN class="number token">6</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="operator token"><</SPAN> <SPAN class="string token">'500000'</SPAN>‍‍‍‍‍ <SPAN class="operator token">AND</SPAN> <SPAN class="token function">LEN</SPAN><SPAN class="punctuation token">(</SPAN>Tot_Length<SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">=</SPAN> <SPAN class="number token">6</SPAN><SPAN class="punctuation token">)</SPAN>‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

Oracle:

LENGTH<SPAN class="punctuation token">(</SPAN>Tot_Length<SPAN class="punctuation token">)</SPAN> <SPAN class="operator token"><</SPAN> <SPAN class="number token">6</SPAN> <SPAN class="operator token">OR</SPAN> <SPAN class="punctuation token">(</SPAN>SUBSTR<SPAN class="punctuation token">(</SPAN>Tot_Length<SPAN class="punctuation token">,</SPAN> <SPAN class="operator token">-</SPAN><SPAN class="number token">6</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">6</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="operator token"><</SPAN> <SPAN class="string token">'500000'</SPAN>‍‍‍‍‍‍‍‍‍ <SPAN class="operator token">AND</SPAN> LENGTH<SPAN class="punctuation token">(</SPAN>Tot_Length<SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">=</SPAN> <SPAN class="number token">6</SPAN><SPAN class="punctuation token">)</SPAN>‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

I know this isn't very elegant, however it does work. I hope it helps.

ChuckBenton

Thanks again. I have a new error now, see below:

arcpy.SelectLayerByAttribute_management("PAAG_-_Feature_Layer\PAAG_-_Feature_Layer", "NEW_SELECTION", "WHERE CAST (TOT_VALUE as INTEGER) > 500000")
Runtime error
Traceback (most recent call last):
File "<string>", line 1, in <module>
File "c:\program files\arcgis\pro\Resources\arcpy\arcpy\management.py", line 6314, in SelectLayerByAttribute
raise e
File "c:\program files\arcgis\pro\Resources\arcpy\arcpy\management.py", line 6311, in SelectLayerByAttribute
retval = convertArcObjectToPythonObject(gp.SelectLayerByAttribute_management(*gp_fixargs((in_layer_or_view, selection_type, where_clause, invert_where_clause), True)))
File "c:\program files\arcgis\pro\Resources\arcpy\arcpy\geoprocessing\_base.py", line 506, in <lambda>
return lambda *args: val(*gp_fixargs(args, True))
arcgisscripting.ExecuteError: ERROR 000358: Invalid expression
ERROR: code:504, Your request has timed out., Proxy server got bad address from remote server (verify the server is running).
Failed to execute (SelectLayerByAttribute).

While I still have an "Invalid Expression" error, I also now have a time out. I think this may be because I'm querying a huge (100+M records) layer. I'm also trying to figure out how to limit my query to only the visible extent of the map. I started another request here: https://community.esri.com/message/790143-arcgis-pro-how-to-limit-select-by-attribute-to-the-visible-extent

Thoughts?

JoshuaBixby

You have an extra quotes in the ArcPy code you pasted, which would generate the error you see. Trying copying and pasting this:

arcpy.SelectLayerByAttribute_management("PAAG_-_Feature_Layer\PAAG_-_Feature_Layer", "NEW_SELECTION", "WHERE CAST (TOT_VALUE as INTEGER) > 500000")

ChuckBenton

Its a text field, as are all the user defined fields in this layer. Thats how the content provider has formatted this data for years.

DanPatterson_Retired

Why was the field a text field in the first place?

Are there other non-numeric values in there?

ChuckBenton

Progress: I needed to refer to TOT_VALUE inside quotes. But now it says the expression is invalid.

arcpy.SelectLayerByAttribute_management("PAAG_-_Feature_Layer\PAAG_-_Feature_Layer", "NEW_SELECTION", 'WHERE CAST ("TOT_VALUE" AS INTEGER) > 500000')
Runtime error
Traceback (most recent call last):
File "<string>", line 1, in <module>
File "c:\program files\arcgis\pro\Resources\arcpy\arcpy\management.py", line 6314, in SelectLayerByAttribute
raise e
File "c:\program files\arcgis\pro\Resources\arcpy\arcpy\management.py", line 6311, in SelectLayerByAttribute
retval = convertArcObjectToPythonObject(gp.SelectLayerByAttribute_management(*gp_fixargs((in_layer_or_view, selection_type, where_clause, invert_where_clause), True)))
File "c:\program files\arcgis\pro\Resources\arcpy\arcpy\geoprocessing\_base.py", line 506, in <lambda>
return lambda *args: val(*gp_fixargs(args, True))
arcgisscripting.ExecuteError: ERROR 000358: Invalid expression
Failed to execute (SelectLayerByAttribute).

I thought that maybe the "WHERE" was not needed, tried it without that, same result.

ChuckBenton

Josh, Thanks. I've tried:

arcpy.SelectLayerByAttribute_management("PAAG_-_Feature_Layer\PAAG_-_Feature_Layer", "NEW_SELECTION", "WHERE CAST (TOT_VALUE as INTEGER) > 500000"")

but get

Parsing error
SyntaxError: EOL while scanning string literal (<string>, line 1).

I started with the layer not in quotes (which is how I think it should be)... same error.

Suggestion as to what I'm doing wrong?

Chuck

JoshuaBixby

You can do more with ArcPy than what the GUI will let you. Since the ArcGIS relational data store is backed by PostgreSQL, try the following in the Python window:

arcpy<SPAN class="punctuation token">.</SPAN>SelectLayerByAttribute_management<SPAN class="punctuation token">(</SPAN>layer<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"NEW_SELECTION"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"CAST (Tot_Value as INTEGER) > 500000"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

mvolz47

The Cast statement does not appear to be helpful as the OP still gets an error.

Chuck:

Have you tried working with ESRI technical support on this issue?

ChuckBenton

Yep, but it needs to be viable in the ArcGIS Pro Select by Attribute SQL Expression Window.

ChuckBenton

One would think so but I still get an error

Curiously, if I drop the WHERE, I get a different error.

In both cases the fact that 500000 is in red is a flag. I've tried smaller numbers, decimal points, etc. without success.

PeteCrosier

You can CAST in SQL:

CAST(SomeField AS INT) >= 500000

mvolz47

What if your system is locked down and schema changes such as this require extensive testing to downstream apps? This seems like functionality that should be built into the software and not require the addition of almost a duplicate field.

DanPatterson_Retired

<SPAN class="keyword token">def</SPAN> <SPAN class="token function">func</SPAN><SPAN class="punctuation token">(</SPAN>val<SPAN class="punctuation token">,</SPAN> threshold<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="string token">"""convert and compare"""</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">if</SPAN> val <SPAN class="keyword token">is</SPAN> None<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; out <SPAN class="operator token">=</SPAN> val
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">elif</SPAN> int<SPAN class="punctuation token">(</SPAN>val<SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">>=</SPAN> threshold<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; out <SPAN class="operator token">=</SPAN> <SPAN class="number token">1</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">else</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; out <SPAN class="operator token">=</SPAN> <SPAN class="number token">0</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">return</SPAN> out


<SPAN class="comment token"># expression</SPAN>
<SPAN class="comment token"># func(!YourTextField!, 50000)</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

make a new field and figure out what you want to do when the threshold is exceeded as in the example above

ChuckBenton

Yep... just gets an syntax error....

DanPatterson_Retired

'int("Tot_Value") >= 500000' would be too easy I suppose (or some variant)

But you don't get the option in the sql builder, So I suspect adding a new field and doing the conversion and test in a def is your best option

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.