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

Question

Hi,

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.

Thanks, Rich

Answer 1

A simple answer is to use collation casting; in your case, use "accent insensitive" collations, e.g.:

SELECT *
FROM table
WHERE ProductName COLLATE SQL_Latin1_General_Cp1_CI_AI LIKE 'Chateau' COLLATE SQL_Latin1_General_Cp1_CI_AI;

Check your collations and adjust them as necessary (notice the ending "_AI" which means "accent insensitive").

A more advanced option would be to use Full-Text Search. Let us know if you need help with that.

Answer 2

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