Different Definition Expression Case Sensitivity Depending on Data Source

[MarkSmith]

Hello,

I have a map service published in ArcGIS Server 11.5 with several spatial layers in it.  Some of the layers are stored on a SQL Server database and some are stored in a File Geodatabase.

I'm using the JavaScript API to set definitionExpression on the layers and I've noticed that the layers in the map service who's data source is SQL Server will let me apply definition expressions that are not case sensitive, whereas the layers with a data source of a File Geodatabase ARE case sensitive.

This inconsistency is a bit of a pain because users can input the text they wish to search for, and asking the user to know in advance about the case of the text is problematic - they shouldn't have to know, and perhaps can't rely on the attribute values being stored in a consistent manner anyway.  For example, they may wish to search for the word "farm", but the text field being searched might be storing "Farm" as well as "farm" and so not all the features will be returned in the results, but only for the File Geodatabase layers.  So in this example the query being applied via mySubLayer.definitionExpression will be DESCRIPTION like '%farm%'.

I was wondering if anyone else has had this problem and how to solve it so that no matter what the data source, setting a definitionExpression is NOT case sensitive?

Thank you.

[JoshuaBixby · Accepted solution]

ArcGIS software does not set or determine the collation of data sources.  The default collation of most data sources is case sensitive, which you are seeing with the file geodatabase.  For RDBMS, SQL Server and MySQL are commonly case insensitive while Oracle, PostgreSQL, and DB2 are commonly case sensitive.

Unless you are able to enforce specific collations of data sources, or force how users input data through validation rules or similar, it is best to assume all queries will be case sensitive and adjust your queries accordingly.

I am not a JavaScript developer, so I can't say for sure, but I believe SQL UPPER and LOWER functions will work within a definitionExpression.

[JoelBennett · Accepted solution]

Yes, extending what @JoshuaBixby has already said, if you had the user-specified value stored in a variable called "userValue", then you'd have something like this:

layer.definitionExpression = "LOWER(DESCRIPTION) LIKE '%" + userValue.toLowerCase() + "%'";

You might also need to add some additional logic to accommodate for the user value containing an apostrophe (i..e. single quotation) mark as well.

[MarkSmith]

Thanks both.  I was secretly wishing for a simple property to set when applying the query rather than coping with restructuring a potentially long and variable query string, but never mind.  Your explanations and suggestions are very helpful, thank you.

[JoelBennett]

Glad to help out where possible.

Note that it is possible to mark more than one post as a solution.  It's up to you, but Joshua's post might qualify as well since he brought up the SQL methodology, which is the best way to handle case-sensitivity issues.