Select to view content in your preferred language

Definition query not case sensitive

1181
8
05-10-2023 08:17 AM
Labels (2)
AndrewWallick
Occasional Contributor II

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?

 

AndrewWallick_0-1683731483663.png

 

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:

AndrewWallick_1-1683731733064.png

 

 

8 Replies
JohannesLindner
MVP Frequent Contributor

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()

Have a great day!
Johannes
DavidPike
MVP Frequent Contributor

Is it an SQL EGDB by any chance?

AndrewWallick
Occasional Contributor II

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.

0 Kudos
DavidPike
MVP Frequent Contributor

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

"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.

0 Kudos
AndrewWallick
Occasional Contributor II

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

@DavidPike @JohannesLindner 

0 Kudos
DavidPike
MVP Frequent Contributor

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

0 Kudos
AndrewWallick
Occasional Contributor II

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

0 Kudos
DavidPike
MVP Frequent Contributor

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

0 Kudos