views:

284

answers:

2

I have a list of company names and the user has to enter his company name to get into the system. Let's say we have the company "Pré ABC", now I want the user to be able to type "Pre" or "Pré".

First I thought this was build-in functionality of the LIKE statement, but unfortunately it isn't. Any thoughts?

+5  A: 

This has to do with collation. Each database has its own collation (and any column can override that collation, too). In your case, you're looking for a collation that's not accent-sensitive, and not case-sensitive. Try configuring the database to "SQL_Latin1_General_CP1_CI_AI". That decodes as "code page 1, case-insensitive, accent-insensitive", which should make your queries work as desired.

jalbert
Beat me to it while I was looking up the full syntax for the collation order. This also has the advantage of being database global, so the entire system works this way.
Godeke
This seems to work. I however didn't do it on the full database but on the specific comparrison with: table.X COLLATE SQL_Latin1_General_CP1_CI_AI = @Name COLLATE SQL_Latin1_General_CP1_CI_AI
Zyphrax
+3  A: 
SELECT  1
WHERE   N'Pré ABC' COLLATE LATIN1_GENERAL_CI_AI LIKE N'%Pre%'
Quassnoi
simple but great example.
Sung Meister