Are Def Query using SQL now different?

[DougBrowning]

I am trying a simple query to look for accidental lower case errors.  For example Pf098788 should be PF098788.

I made a simple query UPPER(UnknownCode) <> UnknownCode and I get no results.  If I try UPPER(UnknownCode) = UnknownCode I get everything.

Layer us a AGOL Hosted feature service.

What am I missing here?

Thanks

[JoshuaBixby]

You have tried this against AGOL hosted feature services in the past and it worked?

[DougBrowning]

Nope never tried this exact query.  Used others just fine though.

[JoshuaBixby]

Not to ask the obvious, but you know there are accidental lowercase records? 

[DougBrowning]

Yea I have been manually changing them all day.  My guess is UPPER is not working right since if I use = it gives me AF and Af back.  <> I get 0 records.

[JoshuaBixby]

I just did some testing and see what you mean.  I don't think the issue is UPPER because if I do something like UPPER(field) = 'XYZ', I get 'xyz' and 'XYZ' returned.  I think the issue has something to do with referencing the field on the right-hand size of the SQL statement.

[MarisaClaggett]

Hi Doug,

Are you doing this calculation via ArcGIS Pro or in AGOL?  In AGOL, the calculation for SQL is simply UPPER(<field name>).

I used the following example SQL to calculated the hosted feature layer field in Pro as well.

UPPER(NOTES)

If using Select by attributes, I can certainly see the issue.  

Best of luck,

Marisa

[DougBrowning]

Not a field calc - this is a Def Query.  

I did notice it keeps defaulting to SQL for field calc, which is weird.  Is there a setting to make this default to Python.

[ThaddeusBara]

By definition, SQL Server is case insensiitve.  Is that perhaps what you are missing?

[DougBrowning2]

If you Def Query a SQL server then yes.  If you Def Query a GDB then no it is case sensitive.  The backend of AGOL is neither i think.  I could see that it may act that way though.  If I try to use COLLATE Latin1_General_CS_AS it says invalid SQL.