Find result containing foregin characters

• naveed.kamboh

  SSC Enthusiast

  Points: 128

  More actions

  May 12, 2008 at 5:39 am

  #380443

  I've got data in English but that contains Some French characters too.

  for example

  Actual data

  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

  Search input would be:

  The Coleg Sir Gar (â replaced by a (English))

  In data, anywhere there could bey a foregin character and that could be any foreign character How can I included these records in my Search results Using SQL or T-SQL. I am using SQL server 2005 standard.

  any help will bhe appreciated.

  Naveed Kamboh
  

• Michael Earl-395764

  SSC Guru

  Points: 53873

  More actions

  May 12, 2008 at 7:24 am

  #813776

  Specify an accent insensitive collation:

  [font="Courier New"]CREATE TABLE #tmp (c varchar(30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL)

  INSERT #tmp VALUES ('The Coleg Sir Gâr')

  SELECT * FROM #tmp WHERE c like '%gar%'[/font]

• naveed.kamboh

  SSC Enthusiast

  Points: 128

  More actions

  May 12, 2008 at 7:43 am

  #813790

  THANKS.

  Its 100% working.

  Can I add something that We can do that without #tmp table We can use query

  where (filedname COLLATE SQL_Latin1_General_CP1_CI_AI ) like '%Gar%'

  [Source]

  http://lists.evolt.org/archive/Week-of-Mon-20070430/189725.html

  Naveed Kamboh
  

• Michael Earl-395764

  SSC Guru

  Points: 53873

  More actions

  May 12, 2008 at 12:16 pm

  #813996

  I meant you should actually change the collation setting for your column - if the sort and sensitivity change will not break anything else.

  If you do what you are suggesting, the table indexes cannot be used for any searches.

  Of course, if you start your search criteria with a wildcard, the indexes are out anyway.