使用CONTAINSTABLE转义SQL Server全文搜索查询中的&符号 [英] Escaping an ampersand in SQL Server Full-Text Search query using CONTAINSTABLE

问题描述

我有一个非常奇怪的例子。我的ASP.NET页面调用我们的存储过程，对我们的数据库执行全文搜索查询。一些通常搜索的字符串包括一个&符号，因为我们的几个品牌产品（知名品牌）的名称中都有一个& 。

事实证明，在某些情况下，除非我转义符号（ \& ），而在另外一种情况下，只有当我跳过&符时，我才会得到任何结果。

I don不知道这是否相关，但是（没有给出品牌名称）以& b 结尾，另一个以& c结尾。

有可能这些字符串（& b 或& c ）有自己的特殊含义吗？而通过转义他们，我实际上是传递一个特殊的字符串到T-SQL？



编辑

其他信息：经过进一步测试，我证明错误在存储过程本身。用& 或 \& 调用它会产生不同的结果。

我会尝试发布存储过程的选定部分。我不会发表全部内容，因为大部分内容并不真正相关。

vParamBuca 参数是导致麻烦的一个参数。值可以是'字母&字母'或字母\\&字母。

  SET @ricercaA ='''FORMSOF（INFLECTIONAL，''+ 
 REPLACE（LTRIM（RTRIM（@vParamBuca ）），''，'）AND FORMSOF（INFLECTIONAL，''）+'）'''
  

变量 @ricercaA 然后用于创建查询字符串：

  FROM产品AS FT_TBL 
 LEFT OUTER JOIN CONTAINSTABLE（Products，Sign1，'+ @ricercaA +'）AS ColSign1_0 ON FT_TBL.ID = ColSign1_0。[KEY] 
 LEFT OUTER JOIN CONTAINSTABLE（Products，ManufacturerAdditionalText，'+ @ricercaA +'）AS ColManufacturerAdditionalText_0 ON FT_TBL.ID = ColManufacturerAdditionalText_0。[KEY] 
 LEFT OUTER JOIN CONTAINSTABLE（Products，ManufacturerForSearch，'+ @ricercaA +'） AS ColManufacturer_0 ON FT_TBL.ID = ColManufacturer_0。[KEY] 
 LEFT OUTER JOIN CONTAINSTABLE（Products，TuttaLaRiga，'+ @ricercaA +'）AS ColTuttaLaRiga_0 ON FT_TBL.ID = ColTuttaLaRiga_0。[KEY] 
 [。 ..] 
  

编辑2

非常感谢@srutzky指引我朝着正确的方向发展！与此同时，我还发现一个数据不一致，其中一个名称中<& 的品牌被修改为没有& ，另一个没有修改（底线，我目前的问题是由于：某人以前曾经做过部分修复）。

无论如何，回到正轨。现在我明白 CONTAINSTABLE 函数中的& 字符被视为逻辑AND（非按位）。

我仍然需要一个解决方案。 这个答案给出了解决方案不适合我（条件与我的不一样）。我如何执行 CONTAINSTABLE 搜索字符串中带&符的内容？最好不必将&符号转换为另一个安全字符？

解决方案

你看到的奇怪行为很可能是由于< a href =https://msdn.microsoft.com/en-us/library/ms187787.aspx =nofollow> CONTAINS 和 CONTAINSTABLE 函数（都与SQL Server的全文搜索功能一起使用）使用&符号（& ）字符等同于 AND 运算符。以下语句来自 CONTAINS 的文档：
$ b

&符号&）可以用来代替AND关键字来表示AND运算符。

没有提及任何转义字符（反斜杠通常不是SQL中的转义字符）。

---

更新

根据问题编辑2中提供的信息以及其他研究，我会说你不是他们需要逃避任何事情。似乎将搜索短语放在双引号中（作为使用 FORMSOF 的结果）将& 视为根据& 两边的值，可以是文字或是断字符。尝试以下示例：

  DECLARE @Term NVARCHAR（100）; 
 
 SET @Term = N'bob& sally'; -  48行
 --SET @Term = N'bob \& sally'; -  48行
 --SET @Term = N'r& f'; -  4行
 --SET @Term = N'r \& f'; -  24行
 
 SET @Term = N'FORMSOF（INFLECTIONAL，''+ @Term +'）'; 
 
 SELECT * FROM sys.dm_fts_parser（@Term，1033，0，0）; 
 SELECT * FROM sys.dm_fts_parser（@Term，1033，0，1）; 
 SELECT * FROM sys.dm_fts_parser（@Term，1033，NULL，0）; 
 SELECT * FROM sys.dm_fts_parser（@Term，1033，NULL，1）; 
  

结果为 bob& sally 和 bob \ sally 是相同的，并且在这两种情况下 bob 和 sally
$ b $ p r& f 和 r \& f 并不相同。因为 r 和，所以 r& f 只会被视为单一的完全匹配字符串。 f 本身并不是已知的单词。另一方面，由于 \ 是断字符，因此在反斜杠中添加两个字母是分开的，在这种情况下，您同时获得 r 和 f 。

鉴于您在Update中声明了数据不一致，其中名称中带有&的品牌之一被修改为不具有&，而另一个品牌不是，我怀疑当您执行 not 在 \ 字符中添加 not 修饰的品牌（因为它完全匹配全职）。但是当你在 \ 字符中添加 do 时，你会得到 修饰为具有& 已被删除，因为您现在正在搜索两个部分，每部分都与该品牌名称的一部分相匹配。

I会修正数据以保持一致：更新已删除<& 的品牌名称，以便重新放入&符号。然后，当用户使用& amp ; 没有额外的 \ 添加，它将完全匹配。这种行为将包含在数据中，并且不需要添加代码来绕过FTS的自然操作，这似乎是一种容易出错的方法。

I have a very peculiar case. My ASP.NET page calls a stored procedure of ours that performs a Full-Text Search query on our database. Some of the commonly searched strings include an ampersand because a few brands of our products (well-known brands, too) have an & in their name.

It turns out that in a certain case I get no results unless I escape the ampersand (\&), and in a certain other case I get no results only if I escape the ampersand.

I don't know if this is relevant, but (without giving out the brand names) one ends in &b and the other one in &c.

Is it possible that these strings (&b or &c) have some special meaning of their own? And that by escaping them I'm actually passing a special string to T-SQL?

EDIT

Additional info: after further testing, I proved that the error is in the stored procedure itself. Calling it with & or \& yields different results.

I'll try to post selected parts of the stored procedures. I won't post it all, because most of it isn't really relevant.

The vParamBuca parameter is the one that causes the troubles. Values could be 'word&letter' or word\&letter.

SET @ricercaA = '''FORMSOF(INFLECTIONAL,"' +
    REPLACE(LTRIM(RTRIM(@vParamBuca)),' ', '") AND FORMSOF(INFLECTIONAL,"') + '")'''

The variable @ricercaA is then used to create the query string:

[...]
FROM Products AS FT_TBL
LEFT OUTER JOIN CONTAINSTABLE (Products, Sign1, '+ @ricercaA + ') AS ColSign1_0 ON FT_TBL.ID = ColSign1_0.[KEY]
LEFT OUTER JOIN CONTAINSTABLE (Products, ManufacturerAdditionalText, '+ @ricercaA + ') AS ColManufacturerAdditionalText_0 ON FT_TBL.ID = ColManufacturerAdditionalText_0.[KEY]
LEFT OUTER JOIN CONTAINSTABLE (Products, ManufacturerForSearch, '+ @ricercaA + ') AS ColManufacturer_0 ON FT_TBL.ID = ColManufacturer_0.[KEY]
LEFT OUTER JOIN CONTAINSTABLE (Products, TuttaLaRiga, '+ @ricercaA + ') AS ColTuttaLaRiga_0 ON FT_TBL.ID = ColTuttaLaRiga_0.[KEY]
[...]

EDIT 2

Many thanks to @srutzky for pointing me in the right direction! In the meanwhile, I also found a data inconsistency where one of the brands with the & in its name was modified not to have the &, and the other one wasn't modified (bottom line, my current problem is caused by that: a partial fix that was made by someone in the past).

Anyway, back on track. Now I understand that the & character in the CONTAINSTABLE function is treated as a logical AND (non bitwise).

I still need a solution for that. This answer gives a solution that doesn't work for me (the conditions are not the same as mine). How could I perform a CONTAINSTABLE search for a string with an ampersand in it? Preferably without having to transform the ampersand to another safe character?

解决方案

The odd behavior you are seeing is most likely due to the CONTAINS and CONTAINSTABLE functions (both used with SQL Server's Full Text Search feature) using the ampersand ( & ) character as equivalent to the AND operator. The following statement is taken from the documentation for CONTAINS:

The ampersand symbol (&) may be used instead of the AND keyword to represent the AND operator.

There is no mention of there being any escape character for it (and a back-slash isn't typically an escape character in SQL anyway).

---

UPDATE

Based on the information now provided in "Edit 2" of the Question, and additional research, I would say that you do not need to escape anything. It seems that putting the search phrases in double-quotes (as a result of using FORMSOF) treats the & as either a literal or a word-breaker, depending on the values on both sides of the &. Try the following examples:

DECLARE @Term NVARCHAR(100);

SET @Term = N'bob&sally'; -- 48 rows
--SET @Term = N'bob\&sally'; -- 48 rows
--SET @Term = N'r&f'; -- 4 rows
--SET @Term = N'r\&f'; -- 24 rows

SET @Term = N'FORMSOF(INFLECTIONAL,"' + @Term + '")';

SELECT * FROM sys.dm_fts_parser(@Term, 1033, 0, 0);
SELECT * FROM sys.dm_fts_parser(@Term, 1033, 0, 1);
SELECT * FROM sys.dm_fts_parser(@Term, 1033, NULL, 0);
SELECT * FROM sys.dm_fts_parser(@Term, 1033, NULL, 1);

The results for bob&sally and bob\&sally are the same, and in both cases bob and sally are separated and never combined into a single exact-match string.

The results between r&f and r\&f, however, are not the same. r&f is only ever treated as a single, exact-match string because r and f alone are not known words. On the other hand, adding in the back-slash separates the two letter since \ is a word-breaker, in which case you get both r and f.

Given that you stated in the Update that you have "data inconsistency, where one of the brands with the "&" in its name was modified not to have the "&", and the other one wasn't", I suspect that when you do not add in the \ character you get the brand that was not modified (since it is an exact match for the full term). But when you do add in the \ character, then you get the brand that was modified to have the & removed, since you are now searching on both pieces, each one matching part of that brand name.

I would fix the data to be consistent: update the brand names that had the & removed to put the ampersands back in. Then when people search using & without the extra \ added, it will be an exact match. This behavior will be consisted across the data, and will not require you adding code to circumvent the natural operation of FTS, which seems to be an error-prone approach.

这篇关于使用CONTAINSTABLE转义SQL Server全文搜索查询中的&符号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！