T-SQL contains search and German Umlaut on SQL Server 2008 R2

Question

I am having a problem with SQL Server 2008 R2, where I have to use search search, ignoring German Umlaute-Letters (ä, ö, ü).

For all non-German developers: German letters Umlaut-Letters can be represented as a regular base letter (a when using ä) and e. Thus, Müller is the same as Müller, and Becker is the same as Becker.

What we want to do is: When searching for “Muller,” we would like to find data containing “Muller,” as well as “Muller,” and when entering “Muller,” we would like to find records containing “Muller,” as well as Muller.

When comparing data using like or = you simply add COLLATE German_PhoneBook_CI_AI. Using a full text index search is not so simple. You can set the sensitivity of the accent to off, but then the search treats the ü-Letter as u, a-letter as a and letter ö such as o, it will not find entries containing oe instead of ö, ue instead of ü and ae instead of ä.

Setting the column mapping to "German_Phonebook_CI_AS" or to "German_Phonebook_100_CI_AS" does not help either.

Has anyone had the same problem before?

Answer 1

You must follow these steps:

• Create a full-text catalog with accent sensitivity
• ,
• 'FORMSOF (INFLECTIONAL, yourquery)'

. :

CREATE TABLE MyTable (
    ID int IDENTITY CONSTRAINT PK_MyTable PRIMARY KEY,
    Txt nvarchar(max) COLLATE German_PhoneBook_100_CI_AI NOT NULL
)

INSERT INTO dbo.MyTable
VALUES (N'Müller'), (N'Mueller'), (N'Muller'), (N'Miller')

GO
CREATE FULLTEXT CATALOG FTSCatalog WITH ACCENT_SENSITIVITY=ON AS DEFAULT
CREATE FULLTEXT INDEX ON MyTable (Txt LANGUAGE German) KEY INDEX PK_MyTable
GO
WHILE EXISTS (
    SELECT * FROM sys.dm_fts_index_population
    WHERE database_id=DB_ID()
    AND status<>7
) WAITFOR DELAY '0:0:1'

GO
SELECT * FROM CONTAINSTABLE(dbo.MyTable,Txt,N'FORMSOF(INFLECTIONAL,Müller)')
SELECT * FROM CONTAINSTABLE(dbo.MyTable,Txt,N'FORMSOF(INFLECTIONAL,Muller)')
SELECT * FROM CONTAINSTABLE(dbo.MyTable,Txt,N'FORMSOF(INFLECTIONAL,Mueller)')

GO
DROP FULLTEXT INDEX ON MyTable
DROP FULLTEXT CATALOG FTSCatalog 
GO
DROP TABLE dbo.MyTable

Answer 2

Müller, Mueller.