How do I perform an accent insensitive compare (e with è, é, ê and ë) in SQL Server?

Coerce to an accent insensitive collation

You'll also need to ensure both side have the same collation to avoid errors or further coercions if you want to compare against a table variable or temp table varchar column

and because the constant value will have the collation of the database Update: only for local variables, not for constants nope, not even then

SELECT *
FROM Venue
WHERE
   Name COLLATE Latin1_general_CI_AI Like '%cafe%' COLLATE Latin1_general_CI_AI

By applying a specific accent insensitive collation to your select:

SELECT * 
FROM Venue 
WHERE Name COLLATE Latin1_General_CI_AI Like '%cafe%' COLLATE Latin1_General_CI_AI

The CI stands for "Case Insensitive" and AI for "Accent Insensitive".

Accent Sensitive and Accent Insensitive searching can be don by using Latin1_general_CI_AI

Here AI is for Accent Insensitive and AS for Accent Sensitive ie, Café and Cafe are the same if Accent Insensitive.

In the below query Latin1_general_CI_AI can be break down into the following parts.

• latin1 makes the server treat strings using charset latin 1, basically ascii.

• CI specifies case-insensitive, so "ABC" equals to "abc".

• AI specifies accent-insensitive,so 'ü' equals to 'u'.

Your query should be as follows:

SELECT * FROM Venue WHERE Name COLLATE Latin1_general_CI_AI Like '%cafe%' COLLATE Latin1_general_CI_AI

Expected Result is as follows:

 Id  name
 1  Café
 2  Cafe