I am doing a MS SQL Server Full Text Search query. I need to escape special characters so I can search on a specific term that contains special characters. Is there a built-in function to escape a full text search string ? If not, how would you do it ?
我正在进行MS SQL Server全文搜索查询。我需要转义特殊字符,以便我可以搜索包含特殊字符的特定术语。是否有内置函数来转义全文搜索字符串?如果没有,你会怎么做?
1 个解决方案
#1
25
Bad news: there's no way. Good news: you don't need it (as it won't help anyway).
坏消息:没有办法。好消息:你不需要它(因为它无论如何也无济于事)。
I've faced similar issue on one of my projects. My understanding is that while building full-text index, SQL Server treats all special characters as word delimiters and hence:
我在其中一个项目中遇到过类似的问题。我的理解是,在构建全文索引时,SQL Server将所有特殊字符视为字分隔符,因此:
- Your word with such a character is represented as two (or more) words in full-text index.
- 带有这样一个字符的单词在全文索引中表示为两个(或更多)单词。
- These character(s) are stripped away and don't appear in an index.
- 这些字符被剥离,不会出现在索引中。
Consider we have the following table with a corresponding full-text index for it (which is skipped):
考虑我们有下面的表及其相应的全文索引(跳过):
CREATE TABLE [dbo].[ActicleTable]
(
[Id] int identity(1,1) not null primary key,
[ActicleBody] varchar(max) not null
);
Consider later we add rows to the table:
稍后我们考虑向表中添加行:
INSERT INTO [ActicleTable] values ('digitally improvements folders')
INSERT INTO [ActicleTable] values ('digital"ly improve{ments} fold(ers)')
Try searching:
尝试搜索:
SELECT * FROM [ArticleTable] WHERE CONTAINS(*, 'digitally')
SELECT * FROM [ArticleTable] WHERE CONTAINS(*, 'improvements')
SELECT * FROM [ArticleTable] WHERE CONTAINS(*, 'folders')
and
和
SELECT * FROM [ArticleTable] WHERE CONTAINS(*, 'digital')
SELECT * FROM [ArticleTable] WHERE CONTAINS(*, 'improve')
SELECT * FROM [ArticleTable] WHERE CONTAINS(*, 'fold')
First group of conditions will match first row (and not the second) while the second group will match second row only.
第一组条件将匹配第一行(而不是第二行),而第二组仅匹配第二行。
Unfortunately I could not find a link to MSDN (or something) where such behaviour is clearly stated. But I've found an official article that tells how to convert quotation marks for full-text search queries, which is [implicitly] aligned with the above described algorithm.
不幸的是,我找不到MSDN(或其他)的链接,其中明确说明了这种行为。但是我发现了一篇官方文章,讲述了如何为全文搜索查询转换引号,这些查询与上述算法[隐式]对齐。
#1
25
Bad news: there's no way. Good news: you don't need it (as it won't help anyway).
坏消息:没有办法。好消息:你不需要它(因为它无论如何也无济于事)。
I've faced similar issue on one of my projects. My understanding is that while building full-text index, SQL Server treats all special characters as word delimiters and hence:
我在其中一个项目中遇到过类似的问题。我的理解是,在构建全文索引时,SQL Server将所有特殊字符视为字分隔符,因此:
- Your word with such a character is represented as two (or more) words in full-text index.
- 带有这样一个字符的单词在全文索引中表示为两个(或更多)单词。
- These character(s) are stripped away and don't appear in an index.
- 这些字符被剥离,不会出现在索引中。
Consider we have the following table with a corresponding full-text index for it (which is skipped):
考虑我们有下面的表及其相应的全文索引(跳过):
CREATE TABLE [dbo].[ActicleTable]
(
[Id] int identity(1,1) not null primary key,
[ActicleBody] varchar(max) not null
);
Consider later we add rows to the table:
稍后我们考虑向表中添加行:
INSERT INTO [ActicleTable] values ('digitally improvements folders')
INSERT INTO [ActicleTable] values ('digital"ly improve{ments} fold(ers)')
Try searching:
尝试搜索:
SELECT * FROM [ArticleTable] WHERE CONTAINS(*, 'digitally')
SELECT * FROM [ArticleTable] WHERE CONTAINS(*, 'improvements')
SELECT * FROM [ArticleTable] WHERE CONTAINS(*, 'folders')
and
和
SELECT * FROM [ArticleTable] WHERE CONTAINS(*, 'digital')
SELECT * FROM [ArticleTable] WHERE CONTAINS(*, 'improve')
SELECT * FROM [ArticleTable] WHERE CONTAINS(*, 'fold')
First group of conditions will match first row (and not the second) while the second group will match second row only.
第一组条件将匹配第一行(而不是第二行),而第二组仅匹配第二行。
Unfortunately I could not find a link to MSDN (or something) where such behaviour is clearly stated. But I've found an official article that tells how to convert quotation marks for full-text search queries, which is [implicitly] aligned with the above described algorithm.
不幸的是,我找不到MSDN(或其他)的链接,其中明确说明了这种行为。但是我发现了一篇官方文章,讲述了如何为全文搜索查询转换引号,这些查询与上述算法[隐式]对齐。