Community
Select to view content in your preferred language

Different Definition Expression Case Sensitivity Depending on Data Source

73
1
15 hours ago
MarkSmith
by
Occasional Contributor
15 hours ago

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.

0 Kudos
1 Reply
JoshuaBixby
by MVP Esteemed Contributor
MVP Esteemed Contributor
8 hours ago

ArcGIS software does not 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 common 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.

0 Kudos