topic Re: Definition query not case sensitive in ArcGIS Pro Questions

Definition query not case sensitive

AndrewWallick — Wed, 10 May 2023 15:17:41 GMT

I'm trying to fix the case of these field values to match the domain on that field (which is proper case). But for some reason, when I filter to find just the upper case values, it still includes the ones I've already changed to proper case.

Is this expected, and is there a workaround if so?

I've already tried field calc'ing it with Proper(), it either is too many records or something else is not letting it work.

Here's the domain values, it's all proper case:

Re: Definition query not case sensitive

JohannesLindner — Wed, 10 May 2023 16:17:36 GMT

That isn't expected behavior, no. SQL should be case sensitive.

I couldn't reproduce that behavior (FGDB, 3.1.1), regardless of whether the field has a domain or not.

To solve your immediate problem, you could just use the Calculate Field tool with an expression (Python) like this:

!WA_FLAG!.title()

Re: Definition query not case sensitive

DavidPike — Wed, 10 May 2023 16:31:17 GMT

Is it an SQL EGDB by any chance?

Re: Definition query not case sensitive

AndrewWallick — Wed, 10 May 2023 23:46:12 GMT

If you mean a SQL enterprise geodatabase, then yes. Does that make a difference?

Actually this is a parcel fabric service, but it is a live reference to our SQL SDE.

Re: Definition query not case sensitive

DavidPike — Thu, 11 May 2023 11:57:42 GMT

https://pro.arcgis.com/en/pro-app/latest/help/mapping/navigation/sql-reference-for-elements-used-in-query-expressions.htm

"Strings are case sensitive in expressions, except when run on geodatabases in Microsoft SQL Server. "

I'm not sure of an easy workaround. There's mention of a COLLATION setting on the DBMS itself but that may not be ideal.
It's maybe a bit convoluted or impractical if you have lots of records - but you could copy to an FGDB (copy should preserve OIDs) then run the selection there, obtain the OIDs of those records then use those to select the EGDB records, or what I'd recommend is to simply do as Johannes suggested and Field Calculate all the values to upper regardless of if they already are upper.

Re: Definition query not case sensitive

AndrewWallick — Thu, 11 May 2023 21:40:03 GMT

Unfortunately when I try to run a field calc on the whole table, it fails with Error 99999999

@DavidPike @JohannesLindner

Re: Definition query not case sensitive

DavidPike — Fri, 12 May 2023 11:41:39 GMT

Did you validate the expression? Ensure it's Python not Arcade.

Re: Definition query not case sensitive

AndrewWallick — Fri, 12 May 2023 15:08:49 GMT

Yeah I did change it to Python and it validated fine.

Re: Definition query not case sensitive

DavidPike — Fri, 12 May 2023 15:55:50 GMT

Are you using versioning? I'd try opening an edit session and Field Calculating in that.