Blog

SQL - How to configure case sensitive or case insensitve searches against SQL Server data sources

When we build search screens or filter data against a SQL Server data source, we may want to specify whether Power Apps carries out a case sensitive, or case insensitive search. This post describes the behaviour of case sensitivity when we search data from Power Apps.

When we call the Search, Filter, or LookUp functions against an SQL Server data source, do these functions carry out a case sensitive or case insensitive match? The answer is that it depends on the collation sequence of SQL Server.

This behaviour can catch out app builders. The typical problem that occurs is that app builders want to carry out case insensitive searches, and Power Apps unexpectedly returns results that are case sensitive.

What is a collation sequence?

In SQL Server, a collation defines the sorting rules, case, and accent sensitivity properties for data. The collation influences how SQL Server stores data, and how it matches and sorts output when we query the database.

When we search or filter data from Power Apps using delegable functions and operators, Power Apps delegates the query to SQL Server for execution. SQL Server executes the query based on the collation settings of the data, and this is the reason why the collation matters.

We can apply a default collation at the server and database levels, and we can also apply different collations at the table and column level.

 

An example of a collation is shown beneath. This is is the default collation of a SQL Azure database:

SQL_Latin1_General_CP1_CI_AS
The parts that make up the collation string include

  • CP1 - this specifies the 'code page' number - the code page defines the characters that we can store
  • CI - CI specifies case-insensitive, whereas CS specifies case-sensitive.
  • AS - AS specifies accent-sensitive, whereas AI specifies accent-insensitive.

The following Microsoft documentation describes database collations in full detail.

How to carry out case insensitive searches

To ensure that searches from Power Apps are always case insensitive, we can modify the collation of the table column to use a case insensitive collation.


In the majority cases however, particularly against exisiting production databases, this isn't a viable solution. Therefore, an effective workaround is to create a view and to specify a different collation sequence for the output.

Here's an example definition of a view that converts the output columns to a case insensitive, accent insensitive collation. 

CREATE VIEW vwPropertySearch
   AS
SELECT 
   PropertyID,
   [Address1] COLLATE Latin1_General_CI_AI AS [Address1],
   [Address2] COLLATE Latin1_General_CI_AI AS [Address2],
   [City] COLLATE Latin1_General_CI_AI AS [City],
   [Postcode] COLLATE Latin1_General_CI_AI AS [Postcode]
FROM dbo.Property

 

From Power Apps, we can now carry out a case insensitive search against the columns in this view with the following formula:

Search(vwPropertySearch, 
       txtSearchInputBox.Text, 
       "Address1", "Address2", "City", "Postcode"
)

How to carry out accent insensitive searches

To carry out accent insensitive searches, we can apply the exact same technique from above. This is frequent request because app builders often want to allow users to search data, and to ignore the accents or diacritics in the source data.

One reason why this problem can occur more frequently is because SQL Azure defaults to an accent sensitive collation, and it is less easy to change the collation of a database, compared to an on-premise installation of SQL Server.

https://docs.microsoft.com/en-us/sql/relational-databases/collations/set-or-change-the-database-collation?view=sql-server-2017

Conclusion

The case and accent sensitivity of searches against a SQL database depends on the collation settings of the database. If we want to configure a specifc search to be case or accent sensitive (or insensitive), we can create a SQL Server view and to search against the view.
  •   Categories: 
  • sql
Previous
Next
Related posts