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')
  • 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 '12 at 19:28
  • Thats for you attention, you are correct. Did some edits. – Remigijus Pankevičius Feb 20 '12 at 19:32

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

  • No, I'm not using 'select *', updated a question... Thanks for your double-quoting suggestion, will check later. – Remigijus Pankevičius Feb 22 '12 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. – Remigijus Pankevičius Feb 27 '12 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. – Remigijus Pankevičius Feb 27 '12 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.