How to search for text in sql server when storing different languages

I have a website that has product names containing French and English words. When I store a product name I store it as html encoded in the ProductName field in my SQL Server. For example the word Château is stored as Ch& #226;teau in my database.

If someone wants to search for a product name I htmlencode the search term. This will successfully return a match for the search term Château but if someone types in Chateau (which most english users would) I don't get any results returned from my LIKE statement. This is correct as Chateau does not match Château.

What I would like to be able to do is let someone search for something like the word Château by simply typing Chateau.

Any ideas how I could go about this? I am using asp.net C# 3.5 SP1 and Sql Server 05.

Solution

select 
    product_name 
from 
    products 
where 
    product_name Collate SQL_Latin1_General_CP1_CI_AI like 'Chateau'