Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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')
share|improve this question
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. –  r.pankevicius Feb 20 '12 at 19:32
add comment

1 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

share|improve this answer
    
No, I'm not using 'select *', updated a question... Thanks for your double-quoting suggestion, will check later. –  r.pankevicius 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. –  r.pankevicius 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. –  r.pankevicius Feb 27 '12 at 21:13
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.