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

8500
33
08-06-2018 06:59 AM
ChuckBenton
Occasional Contributor

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? 

0 Kudos
33 Replies
DanPatterson_Retired
MVP Emeritus

'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

0 Kudos
ChuckBenton
Occasional Contributor

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

0 Kudos
DanPatterson_Retired
MVP Emeritus
def func(val, threshold):
    """convert and compare"""
    if val is None:
        out = val
    elif int(val) >= threshold:
        out = 1
    else:
        out = 0
    return out


# expression
# func(!YourTextField!, 50000)

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

MichaelVolz
Esteemed Contributor

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.

0 Kudos
ChuckBenton
Occasional Contributor

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

0 Kudos
PeteCrosier
Occasional Contributor III

You can CAST in SQL:

CAST(SomeField AS INT) >= 500000

ChuckBenton
Occasional Contributor

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.

0 Kudos
RichardFairhurst
MVP Honored Contributor

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.

ChuckBenton
Occasional Contributor

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

0 Kudos