tsql - T-SQL Contains Search and German Umlaut on SQL Server 2008 R2 -

i facing problem on sql server 2008 r2 have use contains search while ignoring german umlaute-letters (ä, ö, ü).

for non german speaking developers: german umlaut-letters can represented regular underlying letter (a when using ä) , e. müller same mueller , bäcker same baecker.

what want this: when searching "müller" find data containing "mueller" "müller" , when entering "mueller" find entries containing "müller" "mueller".

when comparing data using or = append collate german_phonebook_ci_ai. when using contains search full text index not easy. can set accent sensitivity off contains search treats ü-letter u, ä-letter , ö letter o, wont find entries contain oe instead of ö, ue instead of ü , ae instead of ä.

setting collation on column "german_phonebook_ci_as" or "german_phonebook_100_ci_as" not seem either.

has had same problem before?

you should follow these steps:

1. create full-text catalog accent sensitivity
2. create full-text index specifying german language
3. use 'formsof(inflectional,yourquery)' when querying

see following example:

create table mytable (     id int identity constraint pk_mytable primary key,     txt nvarchar(max) collate german_phonebook_100_ci_ai not null )  insert dbo.mytable values (n'müller'), (n'mueller'), (n'muller'), (n'miller')  go create fulltext catalog ftscatalog accent_sensitivity=on default create fulltext index on mytable (txt language german) key index pk_mytable go while exists (     select * sys.dm_fts_index_population     database_id=db_id()     , status<>7 ) waitfor delay '0:0:1'  go select * containstable(dbo.mytable,txt,n'formsof(inflectional,müller)') select * containstable(dbo.mytable,txt,n'formsof(inflectional,muller)') select * containstable(dbo.mytable,txt,n'formsof(inflectional,mueller)')  go drop fulltext index on mytable drop fulltext catalog ftscatalog  go drop table dbo.mytable