Ignore accents in 'where' clause

Question

In our database we have multiple entries with grave accents, carets or carons (aka hačeks). These accents are collectively also known as diacritical marks.

Now our users want to find entries including these diacritical marks when they search for entries without. I'll show this by a simple example:

In our database we have the entry (contact with name)

Millière

...so this name is correct in the country the person lives in.

In our country we do not have any characters with diacritical marks, therefore our user searches for Milliere. No results come up, as è does obviously not match e.

I have no idea how this could be realized as é,è,ê and many more are available (and this is only an example for letter e...).

(The other way would be much easier, as I could simply string replace all letters with caron/haček with the basic one. Obviously, our users do want the correct version of the name in the database, not the crippled one.)

Answer 1

This problem can be solved using accent insensitive collations.

Your database is probably using a AS (Accent Sensitive) collation so by default it will search for the exact match including accents.

You could instruct the WHERE clause to use another collation than the database default by specifying a collation with the comparison.

In this dbfiddle I created an example using the LATIN1 collations but you could use the same approach with the collation you are using by just changing AS into AI for the collation your column is currently using.

Use the Accent Insensitive collation that matches the collation the colummn is using. For example if the column is using SQL_Latin1_General_CP1_CI_AS, use SQL_Latin1_General_CP1_CI_AI and not Latin1_General_CI_AS or Latin1_General_100_CI_AS or any of the variations of those two since the behavior of the non-SQL_ collations will differ in more ways than just accent-insensitivity, and that might not be expected by users.

You can check the current collation in sys.columns.

CREATE TABLE testaccent (name nvarchar(50));
GO
INSERT INTO testaccent (name) VALUES ('Millière') , ('Milliere');
GO
-- returns Miliere
SELECT * FROM testaccent WHERE name = 'Milliere';

-- returns both
SELECT * FROM testaccent WHERE name='Milliere' COLLATE Latin1_General_CI_AI

--only returns Miliere
SELECT * FROM testaccent WHERE name='Milliere' COLLATE Latin1_General_CI_AS

Read through Using SQL Server Collations for more information.

Then again you'd probably want sorting to use this collation (as peufeu noted in the comments) to ensure that "é" sorts with "e". Otherwise, someone who paginates through results in alphabetical order would be surprised not to find the "é" where they expect them to be, but if you only want to touch this query you can add the COLLATE clause to the ORDER BY too.

As noted by Solomon Rutzky in the comments, if this only affects 1 or a few columns, another option is to create a non-persisted computed column that simply repeats the "name" column and provides the accent insensitive collation, and then index the computed column. This avoids the scan caused by changing the collation within the query. Then the query needs to filter on the new column.

Something like:

ALTER TABLE 
dbo.[table_name] ADD [SearchName] datatype_of_name_column 
AS ([Name] COLLATE LATIN1_GENERAL_100_CI_AI)); 

CREATE INDEX [IX_table_name_SearchName] 
ON dbo.[table_name] ([SearchName] ASC);

Or you could also create a view instead of adding a computed column (as jyao prefers).