In our database we have multiple entries with caron/hatschek.
Now our users want to find entries including caron/hatschek 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 caron/hatschek, 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/hatschek with the basic one. Obviously, our users do want the correct version of the name in the database, not the crippled one.)
TopAnswers
Databases
Top Answer
Tom V (imported from SE)
This problem can be solved using [accent insensitive collations](https://msdn.microsoft.com/en-us/library/ms144250(v=sql.105).aspx).
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](http://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=e5f7fe8dd99179c44d19189187d6b9b9) 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](https://msdn.microsoft.com/en-us/library/ms144260(v=sql.105).aspx) for more information.
Then again you'd probably want sorting to use this collation (as [peufeu](https://dba.stackexchange.com/users/120712/) 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](https://dba.stackexchange.com/users/30859/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](https://dba.stackexchange.com/users/63896/jyao) prefers).