cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Search in Azure SQL data source is case sensitive

According to the PowerApps docs, "Searching is case-insensitive".  However, when using an Azure SQL data source and using the Search() function in PowerApps against one of the columns, I'm finding that the search in my app is case sensitive instead, which isn't what I want.  

 

Internet searches on the problem suggest that the collation settings on my SQL database ought to be where the problem lies.  I understand that you can set collation at the server, database, and column level in SQL Server.  However, the collation of the database is set to "SQL_Latin1_General_CP1_CI_AS", and I understand that the "CI" means "Case Insensitive".   The collation of the column in question is "<database default>".  So it looks as if everything's set up nicely to be case insensitive.

 

If I use the following SQL query, it returns results with both "Neil" and "neil" in the column in question, so it looks like Azure SQL is behaving as it should:

 

SELECT * FROM dbo.Tasks WHERE Task LIKE '%neil%'

 

I tried making an automatically-generated 3 screen PowerApp using "From Data Source", with the same Azure SQL data source, and searches with this app are case sensitive too, so I know it's not something weird with the particular PowerApp I've built.

 

So where's the problem?  I'm stumped!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Search in Azure SQL data source is case sensitive

Great advice @mogulman about using Profiler.

 

I've just tested this against my Azure SQL database and case-insensitive searches work for me.

I think the data type of of your [dbo.Tasks].[Task] column might be the cause of the problem. If it's of data type 'text', could you change it to 'nvarchar(max)' to see if it helps?

View solution in original post

9 REPLIES 9
Highlighted
Skilled Sharer
Skilled Sharer

Re: Search in Azure SQL data source is case sensitive

How are you issuing the query?  Are you using flow?

 

When I don't know what is going I use SQL Profiler.  Is there anyway you can use Data Gateway and connect to an on-premise SQL Server?  SQL Server Express works.  Make a test Powerapp and a test Task table.

Highlighted
Helper II
Helper II

Re: Search in Azure SQL data source is case sensitive

I'm using the Search() function in PowerApps.  Nothing at all esoteric, and not using Flow at all.

 

I tried creating a new database in Azure SQL, accepting the default for collation (which is case insensitive), and created a test Task table in that.  Then created a new automatically-generated PowerApp using the "From Data" option to keep things simple and as near to the default as possible.  Same thing seen with this scenario - search is case sensitive.

 

I do appreciate your suggestion with the on-prem alternative as a test, @mogulman.  Haven't done it as yet, because it would involve quite a chunk of time and I'm hoping (fingers crossed) that it might be possible to get to the bottom of this without having to do it!

 

Anyone else using PowerApps with Azure SQL?  If so, are you finding the same thing as me?  I'm wondering whether this is something that's happening just to me, or whether it always happens when PowerApps connects to Azure SQL.

 

Highlighted
Super User
Super User

Re: Search in Azure SQL data source is case sensitive

Great advice @mogulman about using Profiler.

 

I've just tested this against my Azure SQL database and case-insensitive searches work for me.

I think the data type of of your [dbo.Tasks].[Task] column might be the cause of the problem. If it's of data type 'text', could you change it to 'nvarchar(max)' to see if it helps?

View solution in original post

Highlighted
Skilled Sharer
Skilled Sharer

Re: Search in Azure SQL data source is case sensitive

I agree you could spend all day setting and experimenting with the profiler.

 

I've found the profiler a valuable tool for optimizing app performance.  PowerApps does some weird stuff with SQL Server.  I was able to make changes to app and immediately measure results.  If you have a complex app I would recommend setting up a copy of your app to use the gateway and an on-premise SQL Server (express works).  You'll send hours running profiler and figuring out what is going on and how to optimize it. 

Highlighted
Skilled Sharer
Skilled Sharer

Re: Search in Azure SQL data source is case sensitive

I would really like to see if changing column type helps.  Since it works directly on Azure SQL I thought for sure PowerApps was issuing some command to make it case sensitive.

 

SELECT * FROM dbo.Tasks WHERE Task COLLATE SQL_Latin1_General_CP1_CS_AS LIKE '%neil%'

Highlighted
Helper II
Helper II

Re: Search in Azure SQL data source is case sensitive

Ah yes!  If I use a column type of nvarchar(max), I do indeed get a case insensitive search in PowerApps.  Nice one!  And a quick Web search suggests that the nvarchar(max) column type allows 2GB's worth of storage, so certainly no problem with capacity.

 

So is that the answer?  Should "text" data types in SQL Server be avoided if you want case insensitive searches?

Highlighted
Super User
Super User

Re: Search in Azure SQL data source is case sensitive

Hi @Interlock

I'm glad that solved your problem!

Yes, I would avoid the use of the text data type. One of the main reasons is that Microsoft recommends against using this data type because it is likely to be discontinued at some point in the future.

https://docs.microsoft.com/en-us/sql/t-sql/data-types/ntext-text-and-image-transact-sql?view=sql-ser...

Highlighted
Helper II
Helper II

Re: Search in Azure SQL data source is case sensitive

Fantastic, @timl - thanks for your help.  I went ahead and backed up my database and used:

 

ALTER TABLE dbo.Task
ALTER COLUMN Task NVARCHAR(MAX)

 ...to convert the "text" column to use the "nvarchar(max)" data type instead, and now my PowerApp has case insensitive searches, just the way I want 'em.

 

@mogulman - thanks for the pointers.  Have made a note to check out SQL Profiler - I wasn't aware of its existence!

Highlighted
Super User
Super User

Re: Search in Azure SQL data source is case sensitive

Hi @mogulman

When I profile a Search against a text column, there appears to be no WHERE clause in the Select statement that PowerApps issues.

 

image.png

I figure PowerApps must be retrieving all the records and performing the search locally. I'm surprised by this behaviour. The next time you run up profiler, could you profile a search against a text column to see whether or not you see the same as I do?

Helpful resources

Announcements
Check this Out

Announcing

Class of 2020- Season 2

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (10,240)