SQL Server Full Text Search Escape Characters ?

Question

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 ?

Answer 1

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:

1. Your word with such a character is represented as two (or more) words in full-text index.
2. 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.