I'm working on a Flex application for a client that will be the replacement for a legacy application they have that was written in .net/adf. In their current code base, they are using an ADF query with a Replace function inside the where clause.

adfQueryFilter.WhereClause = "REPLACE(TMS,'-','') like '%" + ColValue + "%'";

The reason for this is they have multiple parcel tables, and each table has a TMS column that they are querying on. The TMS values for each table have unique number groupings such as ###-##-##-### or ####-##-##-## etc. The consistency is that they all contain 10 digits. The ColValue variable above will contain a sequence of numbers WITHOUT hyphens in it (example: ##########). ColValue only exists in their data this way and cannot be converted to a hyphenated format without configuration rules and coding. That extra config and coding is what I'm trying to avoid if possible, and the Replace function seems to be capable of doing just that - in ADF anyway.

So, the question then is, can REPLACE be used in an ArcGIS Server Query, or is it only supported by ADF?

The following query will generate a selection in ArcMap: Select * from ParcelTable where: REPLACE(TMS,'-','') LIKE '%63%'

However, when I try that same query on the ArcGIS Server rest endpoint it fails.

This is a ArcGIS 10.2.1 map service fyi.

Thanks for any input on this!

  • Can you include the error message you're getting when it fails? Maybe you need to wrap TMS with double quotes? REPLACE("TMS",'-','') LIKE '%63%' – Kevin R Dyke Jun 25 '14 at 17:04
  • This was written in code, using a double quote would have terminated / invalidated the query's string. The syntax is correct and works with the answer below. – lg1382 Sep 8 '14 at 17:08

Of course, right after I post this I find the answer. At 10.2, ESRI automatically enabled Standardized Querying which removes the ability to use database specific functions in queries on ArcGIS Server map services. The instructions to disable this setting can be found on the About standardized queries page:

It should be noted from that page (under the "Disabling standardized queries" heading) that:

By disabling this security option, your site becomes more vulnerable to SQL injection attacks.

so keep that in mind if faced with a similar problem.

I also found a work-around to this that worked in my situation. This work-around allowed me to leave Standardized Querying enabled (meaning no Replace functionality and more secure). I simply added wildcards between each character of the search value which allowed me to change the query to a simple Where TMS = '%6%3%x%%x%...'

Just adding this in hopes it helps someone who cannot disable Standardized Querying due to the security implication.

  • @PolyGeo - It's in the link I referenced in this Post/Answer. It's highlighted under the "Disabling standardized queries" heading. – lg1382 Sep 8 '14 at 17:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.