Sql-server全文CONTAINS + COLLATE忽略重音问题

问题描述

大家好，我在这里挣扎一点，用COLLATE忽略重音，同时也使用Contains全文。

这里的例子，im编码的实际参数只是为了直到我理解它。

如果我有

  SELECT 
 Col1，
标题COLLATE SQL_Latin1_General_Cp850_CI_AI AS标题
 ColX 
 FROM 
 Foo 
 WHERE 
 CONTAINS（（标题），''suenos'或'french''）
  

结果与法语。如果我在例如：

  WHERE 
 CONTAINS（（标题），'suenos * french'）
  

我得到了Sueños和法语的结果。我注意到与LIKE和COLLATE相同的行为，例如它只与suenous％相关的词作为suenos。

这是为什么？

非常感谢。

解决方案

假设：您正在使用sql2005或更高版本。 p>

我相信当你第一次创建索引时，你需要将Accent Sensitivity设置为ON或OFF。

  CREATE FULLTEXT CATALOG WITH ACCENT_SENSITIVITY = OFF 
 GO 
  

或更改它：

 改变全文目录AwCat重建ACCENT_SENSITIVITY = ON 
 GO 
  

您可以在这里找到更多

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.

解决方案

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 ArticlesSQL Server 2005 Full-Text Search: Internals and Enhancements

这篇关于Sql-server全文CONTAINS + COLLATE忽略重音问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！