使用特殊字符进行全文搜索

I have a table with values such as "F-10" or "Jim-beam". Is there a way for me to get these results if a user had searched say "F10" or "Jimbeam"? Basically, the user may not know there is a dash in the entries but I want the search to be forgiving enough to find it.

我有一个表格，如“F-10”或“吉姆梁”。如果用户搜索过“F10”或“Jimbeam”，我有办法获得这些结果吗？基本上，用户可能不知道条目中有破折号，但我希望搜索能够容易找到它。

Right now I'm trying to use:

现在我正在尝试使用：

SELECT *
WHERE
    CONTAINS(table.*, ,'"F10*" Or "Jimbeam*"')

3 个解决方案

#1

you can create array to get the inserted value from the user

您可以创建数组以从用户获取插入的值

then use the like %value1% or like %value2% for each value in the array

然后对数组中的每个值使用类似％value1％或类似％value2％

it could be a solution

它可能是一个解决方案

#2

You could try to replace the values in the database with values that do not contain any special characters. I used the function described in this answer before: https://*.com/a/1008566/894974 So with that function installed, your where-clause would become:

您可以尝试使用不包含任何特殊字符的值替换数据库中的值。我之前使用过这个答案中描述的功能：https：//*.com/a/1008566/894974因此安装了该功能后，你的where子句将成为：

where dbo.RemoveNonAlphaCharacters([columnToSearch]) like '%'+@searchString+'%'

#3

You can create a separate column where dashes are removed from these words, then perform your full text searches against that column.

您可以创建一个单独的列，从这些单词中删除破折号，然后对该列执行全文搜索。

For example, your table could look like this (or the new column could be part of a new table):

例如，您的表可能看起来像这样（或者新列可能是新表的一部分）：

Id  Text            TextForFullTextSearch
-----------------------------------------
1   Jim-beam        Jimbeam
2   F-10            F10
3   blah blah       blah blah

If you need to support searches on both "Jimbeam*" and "Jim-beam*" then you could perform the full text search against both the old and new columns.

如果您需要支持“Jimbeam *”和“Jim-beam *”的搜索，那么您可以针对旧列和新列执行全文搜索。

This does require you to store the text twice so there will be more gears in your process. The benefits will be in the search accuracy and performance (LIKE will be much slower), so you'll have to weigh those benefits against the increased complexity.

这确实需要您将文本存储两次，以便在您的过程中有更多的齿轮。优势在于搜索的准确性和性能（LIKE会慢得多），因此您必须权衡这些优势与增加的复杂性。

Some ideas for populating the new column as data is inserted and updated:

插入并更新了将新列填充为数据的一些想法：

Handle this in your data layer, i.e. all insert and update statements should include both Text and TextForFullTextSearch.
在数据层中处理此问题，即所有insert和update语句都应包含Text和TextForFullTextSearch。
Add an insert/update trigger to the table that, whenever Text is inserted or updated, simultaneously updates TextForFullTextSearch.
向表中添加插入/更新触发器，无论何时插入或更新Text，都会同时更新TextForFullTextSearch。
Create an automated job that continually polls the table for inserts/updates, then updates TextForFullTextSearch accordingly.
创建一个自动作业，不断轮询表中的插入/更新，然后相应地更新TextForFullTextSearch。

#1