Arcgis rest api service - query to check for numeric in a string field

user6XRS 0
Viewed 30

I am using Arcgis rest api service query to return only records that have a numeric value in a string field. (actionid is the string field which can be null also) This is what I am trying so far for the WHERE clause in the ArcGIS REST Services for the feature class:

TEST 1

I tried the following for first test using DECIMAL:

CAST( (COALESCE(actionid , '0')) AS DECIMAL) > -10000000

This is the error I am receiving.

Error:

'where' parameter is invalid

TEST 2

I tried the following for second test using INTEGER:

CAST( (COALESCE(actionid , '0')) AS INTEGER) > -10000000

This is the error I am receiving.

Error:

Unable to perform query. Please check your parameters.

So far from my research, it looks like it should support all the above keywords since it uses SQL-92. What am I missing? Thank You.

arcgis-server arcgis-js-api arcgis
2 Answers

I found the issue!

The problem is that the CAST function will error out if it tries to cast a string to an integer:

CAST( (COALESCE(actionid , '0')) AS INTEGER) > -10000000

I was able to confirm this was the issue by running the same query but on a different field such as objectid that has no strings. This works fine:

CAST( (COALESCE(objectid , '0')) AS INTEGER) > -10000000

So instead of using CAST use the LIKE statement. The following query is the solution and will return numeric values only as desired:

actionid like '%[0-9]%'
user6XRS0

I think you should try another solution like ISNUMERIC function, wich evaluates the parameter and returns 1 if "is" a number, 0 in other case.

With the propose function the solution should be something like,

ISNUMERIC(actionid)=1
cabesuon0
Related