Sql-server Full Text CONTAINS + COLLATE to ignore accents issues

Question

Hi all im struggling a little here with using COLLATE to ignore accents whilst also using Contains full text.

Ive reduced the columns im searching down to just one for the example here, and im hard coding the actual parameter just to simply this until i understand it.

If i have

SELECT 
     Col1,
     Title COLLATE SQL_Latin1_General_Cp850_CI_AI AS Title,
     ColX
FROM
     Foo
WHERE 
     CONTAINS((Title),  '"suenos" OR "french"')

This only returns results with french. If i add the wild card after eg:

 WHERE 
     CONTAINS((Title),  '"suenos*" OR "french"')

I get results for Sueños and for french. Ive noticed the same behaviour with a LIKE and COLLATE, eg it only words with 'suenous%' as apposed to 'suenos'.

Why is this?

Thanks muchly.

Answer 1

Assumption: you are using sql2005 or later.

I believe you need to set Accent Sensitivity as ON or OFF when you first create the index.

CREATE FULLTEXT CATALOG AwCat WITH ACCENT_SENSITIVITY=OFF
GO

or alter it:

ALTER FULLTEXT CATALOG AwCat REBUILD WITH ACCENT_SENSITIVITY=ON
GO

You cand find out more here Microsoft SQL Server 9.0 Technical Articles SQL Server 2005 Full-Text Search: Internals and Enhancements