86

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?

gbn
  • 422,506
  • 82
  • 585
  • 676
ilivewithian
  • 19,476
  • 19
  • 103
  • 165

3 Answers3

129

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
Tomas Kubes
  • 23,880
  • 18
  • 111
  • 148
gbn
  • 422,506
  • 82
  • 585
  • 676
  • 22
    You don't need to provide collation twice in this case: SELECT * FROM Venue WHERE Name Like '%cafe%' COLLATE Latin1_general_CI_AI – PollusB Nov 21 '14 at 15:18
  • I implemented this answer, the search is accent insensitive, but the side effect is that the I am missing some accent in select query. For example c with acute (U+0107). In column properties I see SQL_Latin1_General_CP1_CI_AI collation. I changed the collation using following statement: ALTER TABLE mytable ALTER COLUMN description varchar(48) COLLATE SQL_Latin1_General_CP1_CI_AI. It used to be Czech_CI_AS collation before. If I use the query in the answer without changing the collation of the column, it is not working in Entity framework. – Tomas Kubes Nov 07 '16 at 07:39
54

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".

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
  • 12
    Ah, that's what all those silly characters on the end mean. I know it's daft, but that helps a huge amount. Somehow, for me, DB collation always seems to bring brain fog with it. – ilivewithian Mar 17 '10 at 11:10
10

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
Jino Shaji
  • 1,097
  • 14
  • 27