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 pro开发者_Go百科duct 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.
select
product_name
from
products
where
product_name Collate SQL_Latin1_General_CP1_CI_AI like 'Chateau'
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.
精彩评论