How to find names with special characters like 'José' on MS SQL-Server

Question

If i search for 'Jose' the result is 0. If i search for 'Josè' either and 'Jôse' gives also no result.

select name from TblName where name = 'Jose' or name = 'Josè' or name = 'Jôse'

I know the replacement with ? : where name like 'Jos?' or name like 'J?se' But this is very unhandy and who knows if there is not a special form of 's'?

I want to search for 'Jose' and get all the variants of it with all the possible special characters on all single characters; how do that?

Answer 1

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

By applying a specific collation order to your select:

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

The CI stands for "Case Insensitive" and AI for "Accent Insensitive".

If you are French, this article may also help : Remplacer les accents dans une chaîne.