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

I'm looking to compare two varchars in SQL, one would be something like Cafe and the other Café is there a way i开发者_如何转开发n 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?

---

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