T-SQL Contains Search and German Umlaut on SQL Ser

问题:

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

For all non German speaking developers: The German Umlaut-Letters can be represented as the regular underlying letter (a when using ä) and an e. So Müller is the same as Mueller and Bäcker is the same as Baecker.

What we want to do is this: When searching for "Müller" we would like to find data containing "Mueller" as well as "Müller" and when entering "Mueller" we would like to find entries containing "Müller" as well as "Mueller".

When comparing the data using like or = you just append COLLATE German_PhoneBook_CI_AI. When using a contains search with a full text index it is not that easy. You can set the accent sensitivity to off but then the contains search treats the ü-Letter like an u, the ä-Letter like an a and the ö letter like an o, it wont find the entries that contain oe instead of ö, ue instead of ü and ae instead of ä.

Setting the collation on the column to "German_Phonebook_CI_AS" or to "German_Phonebook_100_CI_AS" does not seem to help either.

Has anyone had the same problem before?

回答1:

You should follow these steps:

1. Create the full-text catalog with accent sensitivity
2. Create the full-text index specifying the German language
3. Use 'FORMSOF(INFLECTIONAL,yourquery)' when querying

See the 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 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

回答2:

I would suggest you search for both Müller and Mueller occurences in this case.