Accent Insensitive search in sql server + COLLATE SQL_Latin1_General_CP1_CI_AI

We were facing a problem in searcing the exact data from Microsoft sql server 2005 database, Our database is containng foreign language characters ,çéèêëñòóôõöàáâãäåìíîïùúûüŵŷýÿ as you can see in following data, I was wondering how we can solve this problem, I tried to search it on the google and posted this in some forums.

The Coleg Sir Gâr  
Sabhal Mòr Ostaig
Château du Rosey
Ecole La Découverte
Colegio Gran Bretaña
St Thérèse of Lisieux RC Primary School

I got the answer to my question  here

To remember it I am posting it to my blog  with the great thanks to
“Ten Centuries” who replied 100% to my problem. He suggested me to create a temp table while I found in other forum ( we can do it in query

where (filedname COLLATE SQL_Latin1_General_CP1_CI_AI ) like ‘%Gar%’




top 10 organisationname as organisationname

from organisation where organisationname like (‘%arete%’)

I am using this in my search query and its working hundred (100) percent.

I was searchuing it with following terms.

Find result containing foregin characters

making a column accent insensitive in a query”

“changing in query for a column COLLATE”

“COLLATE SQL_Latin1_General_CP1_CI_AI”

Tags: , , , , , ,

3 Responses to “Accent Insensitive search in sql server + COLLATE SQL_Latin1_General_CP1_CI_AI”

  1. Ronaldo Canesqui says:

    This solution works fine, the only drawnback is the performance penalty.

  2. Kel Way says:

    Thanks! Solved my problem.

  3. mitica says:

    It works great in SQL Server 2008. Thank you