How to perform comparison without accent (e with è, é, ê and ë) in SQL Server?

Question

I want to compare two varchars in SQL, one of them will be similar to Cafe , and the other Café there is a way in SQL that will allow to compare two values. For example:

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

So, if there is a place called Big Bobs Café Extraordinaire , will it be included in the result set?

Answer 1

Forcing emphasis insensitive juxtaposition

You also need to make sure that both sides have the same sorting to avoid errors or further coercion if you want to compare with a table variable or varchar column of temp table

and because the constant value will be sorted by the Update database: only for local variables, and not for nope constants , not even then

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

Answer 2

Applying a special accent-sensitive matching to your choice:

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

CI stands for Case Insensitive and AI for Accent Insensitive.

Answer 3

if "A" COLLATE SQL_Latin1_General_CP1_CS_AS = 'a' COLLATE SQL_Latin1_General_CP1_CS_AS SELECT N'Yes 'ELSE SELECT N'No'

Answer 4

Accent sensitive and insensitive searches cannot be used with Latin1_general_CI_AI

Here are AI for Accent Insensitive and AS for Accent Sensitive i.e. Café and Cafe are the same if Accent Insensitive.

In the query below, Latin1_general_CI_AI can be divided into the following parts.

• latin1 forces the server to process strings using charset latin 1, mainly ascii.

• CI indicates case insensitive, so "ABC" is equal to "abc".

• AI indicates insensitivity to accent, so 'ü' is equal to 'u'.

Your request should look like this:

 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