Forum Discussion
How to deal with case sensitivity in Dataverse tables without using lower function in queries
Hello antoec
In Dataverse, the collation settings—including whether the data comparisons are case-sensitive or case-insensitive—are defined when the environment is created and cannot be changed afterward. This means that if your environment was configured with a case-sensitive collation, you cannot later switch it to be case-insensitive through a configuration change. Therefore, to resolve issues where related fields have mismatched cases, the only practical solutions are to normalize the data—for example, by ensuring that all key values are stored in the same case—or to use computed columns that store a pre-normalized version of the data. This approach typically avoids the performance overhead of using functions like Lower() or Upper() in every query.
there isn’t a built‐in configuration setting in Dataverse to globally disable case sensitivity for text comparisons. Instead, you must ensure that the data used for linking tables is normalized so that both sides of the join use the same case.
If this is helpful please accept the answer
Super UserHello antoec
In Dataverse, the collation settings—including whether the data comparisons are case-sensitive or case-insensitive—are defined when the environment is created and cannot be changed afterward. This means that if your environment was configured with a case-sensitive collation, you cannot later switch it to be case-insensitive through a configuration change. Therefore, to resolve issues where related fields have mismatched cases, the only practical solutions are to normalize the data—for example, by ensuring that all key values are stored in the same case—or to use computed columns that store a pre-normalized version of the data. This approach typically avoids the performance overhead of using functions like Lower() or Upper() in every query.
there isn’t a built‐in configuration setting in Dataverse to globally disable case sensitivity for text comparisons. Instead, you must ensure that the data used for linking tables is normalized so that both sides of the join use the same case.
If this is helpful please accept the answer
nilendraFabric thank you for the reply. Could guide any documentations or reference where i could follow and create a dataverse which is case-insensitive.
- nilendraFabric
https://learn.microsoft.com/en-us/power-platform/admin/language-collations
key points in the doc
Default Collation Settings:
• When creating a Dataverse environment, administrators select a default language. This choice determines the collation applied to the SQL database that underpins the Dataverse instance.
• Collation settings affect sorting, filtering, and text comparisons, including case sensitivity and accent sensitivity.All languages in Dataverse use case-insensitive collations by default, denoted by `_CI`. For example, “Cafe” and “cafe” are treated as the same word
