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? 

[DanPatterson_Retired]

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

Are there other non-numeric values in there?

[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.

[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'‍‍‍‍‍

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(Tot_Value) > 1‍ OR (RIGHT(Tot_Value, 1) > '5'‍‍‍‍‍‍‍‍ AND LENGTH(Tot_Value) = 1‍)‍

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:

LEN(Tot_Length) >= 6 OR (RIGHT(Tot_Length, 6) > '500000'‍‍‍‍‍‍ AND LEN(Tot_Length) = 6)‍‍‍

For oracle:

LENGTH(Tot_Length) > 6 OR (SUBSTR(Tot_Length, -6, 6) > '500000'‍‍‍‍‍‍‍ AND LENGTH(Tot_Length) = 6)‍‍‍

To compare if a string is smaller than a number:

LEN(Tot_Length) < 6 OR (RIGHT(Tot_Length, 6) < '500000'‍‍‍‍‍ AND LEN(Tot_Length) = 6)‍‍‍‍

Oracle:

LENGTH(Tot_Length) < 6 OR (SUBSTR(Tot_Length, -6, 6) < '500000'‍‍‍‍‍‍‍‍‍ AND LENGTH(Tot_Length) = 6)‍‍‍

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

[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.