Filter a gridview ignoring accents

Question

Hello.

I am trying to filter a gridview with a filter expression like this

"SurName like '%surname%' AND FirstName like '%firstname%'"

So far it works fine except in one edge case: Some name's have an accent in them. We want to display these names with an accent but when searching I would like to use an accent insentive search. How do I do this? I tried useing the SQL COLLATE command and that worked when I did a test query on the database but not in the filter expression for a gridview.

Please could I have some advice?

Answer 1

Before binding the datasource to the gridview add an extra column with de-accented version of the column you want to filter. Then run the filter on the modified column but display the original.