Accent Insensitive search in sql server + COLLATE SQL_Latin1_General_CP1_CI_AI
Partner links
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
Areté
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 http://www.sqlservercentral.com/Forums/Topic498678-338-1.aspx?Update=1
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 (http://lists.evolt.org/archive/Week-of-Mon-20070430/189725.html)that 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: accent insensitive, collate, foreign characters, SQL (Structured Query Language), sql server, sql server 2005, sql server collate