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

Question

I'm looking to compare two varchars in SQL, one would be something like Cafe and the other Café is there a way in SQL that will allow the two values to be compared. For instance:

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

So if there is a venue with the name Big Bobs Café Extraordinaire it would be included in the result set?

Answer 1

By applying a specific collation order to your select:

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

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

Answer 2

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