Are there known issues about UPPER, LOWER SQL functions in ArcGIS 10, file GDB?

Question

Platform: ArcGIS 10 SP3, .NET

I use: IQueryFilter queryFilter = new QueryFilterClass(); with ArcGIS .NET SDK and build a query...

Does anybody had problems using UPPER(stringField) and LOWER(stringField) functions on esri file geodatabases? If so, please share links to some "confirmed-will-fix-in-2-years" pages...

I know we have some "old" warnings in our codebase about that. Like we though UPPER(stringField1)=UPPER(stringField2) doesn't work on file gdb. Last week I ran into it again but it seems I could not get even UPPER(stringField)=('STRING-IN-mixedCACE'.toUpper()) query to find anything in gdb. UPPER(stringField)='STRING-IN-UPPERCACE' works, but the UPPER should be better named NOOP.

Example. There is a record in fgdb where stringField='Abc'. The query UPPER(stringField)='ABC' returns 0 records. (Error: Should be 1!)

PS. Such UPPER/LOWER queries seem to work correctly in ArcMap when using feature layer definition query.

Example 2.

Table (id, stringField)
1, 'Abc'
2, 'ABC'

QueryFilter: where UPPER(stringField)='ABC'
returns: (2, 'ABC')
should-return: (1, 'Abc'), (2, 'ABC')

Answer 1

Are you using the following SQL sytax with iQueryFilter.WhereClause?

Query: select * where UPPER(stringField)='ABC'

Omit Select * Where from the expression. The syntax for IQueryFilter.WhereClause is UPPER("LAST_NAME") = 'JONES'

Also, to query FGDB (as well as D-Base table, Coverage Info table or Shapefile) you must enclose the field name in double quotes.

More here