2

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')
2
  • 3
    Perhaps I am misunderstanding your question, but I am not sure what results you expect.. UPPER(stringField)='mixedCASE' will never match anything. UPPER(stringField='ABC') will never match anything as well. UPPER(stringField)='ABC' is the correct expression.
    – Petr Krebs
    Feb 20, 2012 at 19:28
  • Thats for you attention, you are correct. Did some edits. Feb 20, 2012 at 19:32

1 Answer 1

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

3
  • No, I'm not using 'select *', updated a question... Thanks for your double-quoting suggestion, will check later. Feb 22, 2012 at 8:25
  • I stopped at a breakpoint and used that nice Visual Studio feature immediate window or so. (I tried to q-search for an exact name but it looks other guys like other features so that name may be not exactly correct.) It looks quoted names (LAST_NAME -> "LAST_NAME") do not work either. Feb 27, 2012 at 21:06
  • Then I tried to write a 'failing unit test', but (probably) found there are some issues with setting up licenses in MTA. q-searched... Hrr. There are too way many "powerpoint programmers" in esri... (btw, did you notice that lowercase?!) So this may take some time. Feb 27, 2012 at 21:13

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.