Searching data–What you need to know about case sensitivity
In this post, we'll look at how PowerApps behaves when it comes to case sensitivity. This is important because it can affect the results of comparisons and searches. Here are the top three things you should know about case sensitivity.
1 Beware when making string comparisons
String comparisons ARE case sensitive. Here's an example of where this can be a problem. The formula beneath attempts to apply conditional formatting to a data card. If the gender equals "male", the fill color of the card should be AliceBlue.
As the screenshot shows, the fill color of the card is white. The comparison fails because:
"Male" = "male" -> false
To work around this issue, we can convert the value that we want compare with the Lower function, and carry out our comparison against a lower case string. So in this example, our comparison operator would look like this:
If(Lower(ThisItem.Gender) = "male",
Color.AliceBlue,
Color.Whilte
)
Another common scenario where this occurs is when app builders want to compare email addresses.
2 Column Names are Case Sensitive
Column names ARE case sensitive. As the screenshot beneath shows, IntelliSense displays a warning when we use the incorrect casing for a column name.
As I explain in the link below, I believe this behavior stems from the case sensitive nature of JSON.
3 Searches are Case Insensitive
For SharePoint and most data sources, any searches that we carry out with the Filter and Search functions are NOT case sensitive. To illustrate, here's a standard search screen that uses the Filter function, based on a SharePoint data source. Here, we can see that a search value of "tiM" matches "Tim".
4 SQL Server DataSources are an Exception
Although searches with Filter and Search are generally NOT case sensitive, searches against SQL Server data sources CAN be case sensitive, depending on the collation sequence of the database. In the post beneath, I explain this behavior in a bit more detail.
(Note that this behavior can also apply to on-premises SharePoint data sources that are backed by a SQL Server with a case sensitive collation).
To check the collation sequence of SQL Server, here are the TSQL commands that we can run:
-- 1 This returns the server collation
SELECT SERVERPROPERTY('COLLATION')
-- 2 This returns the collation of a specific database
SELECT collation_name
FROM sys.databases
WHERE name = 'your_database_name'
-- 3 This returns the collation of a table
SELECT COLUMN_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'your_table_name' AND CHARACTER_SET_NAME IS NOT NULL
The collation sequence will look something like this: SQL_Latin1_General_CP1_CS_AS
The 'CS_AS' part of this indicates a case sensitive, accent sensitive collation sequence.
So what can we do if we have a case sensitive SQL data source and want to carry out case insensitive searches from PowerApps? One way is to apply the Lower function against the source field and to match against a lower case criteria value.
Filter(EmployeeTable, Lower(Firstname) = "tim")
However, the big disadvantage of this technique is that it's non-delegable and it won't return accurate results if our source data exceeds 2000 rows. Therefore, a better technique is to create a SQL View and to cast the fields we want to search against to a non case sensitive collation. From within PowerApps, we can then Filter/Search against the view that we created. Here's an example of how such a view definition would look like:
SELECT id,
CAST(Firstname AS NVARCHAR(MAX)) COLLATE SQL_Latin1_General_CP1_CI_AS Firstname,
CAST(Surname AS NVARCHAR(MAX)) COLLATE SQL_Latin1_General_CP1_CI_AS AS Surname,
Salary,
DateOfBirth
FROM dbo.Employee
A useful tip is that we can 'invert' this technique. So if our source database is case insensitive and we want to carry out case sensitive searches from within PowerApps, we can create a view and cast our fields to a case sensitive collation (such as SQL_Latin1_General_CP1_CS_AS).
Conclusion
In conclusion, string comparisons and column names are case sensitive in PowerApps. Searches can be case sensitive or case insensitive, and this depend on the underlying data source.
