ArcGIS Server 10 - Query numeric field with string

Question

In ArcGIS Server 10, I am running queries against several fields in a feature class. All of them are sent with the format FIELDNAME ='value' (all queries contain the single quotes).

This is failing in cases where have numeric data. Is there any kind of setting I can use with the map service to allow me to query numeric data with strings?

• dmsnell: Are you using the REST API to do this? From what source and in what protocol/language are your queries?
• Britt Wescott: I am using the REST API and my data is stored in SQL Server 2008 R2.
• dmsnell: Do you have any error messages? I tried this and found that with single quotes it didn't matter whether or not it was numerical. I verified on double and integer types. One thing, however, is that when I used double quotes it did return no results, but it did so even when querying string and character data types.
• Britt Wescott: No error messages, I just don't get any results.
• dmsnell: Does it return "No results found." If it does, that means that the query has simply failed to find any matching items. If there is something wrong with the query, it should return another message, such as "Unable to perform query. Please check your parameters" Try using the web interface to perform a test REST query - arcgis/rest/services/map/MapServer/0/query?where=field='45'&returnGeometry=true&outFields=name&f=html

Answer 1

You need to cast the field to a varchar first:

CAST("FIELDNAME" as VARCHAR(20)) = '1'

May not work with all data sources, but SQL and FileGDB do work.

• Derek Swingley: My thought is that you should build a proper query rather than adding CAST()s to your where clause.
• Sasa Ivetic: @Derek - I'd build the query properly too personally. However, his question is how to query numeric data with strings (which will result in less-than-ideal performance), which CAST will accomplish. He'll run into issues with knowing what size of VARCHAR to cast everything to though.
• Derek Swingley: good point, I think you nailed it then.