SQL Server Full Text Search Escape Characters?

by

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.

More Related Contents:

  1. Is it possible to use Full Text Search (FTS) with LINQ?
  2. How do you get leading wildcard full-text searches to work in SQL Server?
  3. Full text search does not work if stop word is included even though stop word list is empty
  4. 7645 Null or empty full-text predicate
  5. Using Full-Text Search in SQL Server 2008 across multiple tables, columns
  6. Full text catalog/index search for %book%
  7. What do Clustered and Non-Clustered index actually mean?
  8. Regex pattern inside SQL Replace function?
  9. Does T-SQL have an aggregate function to concatenate strings? [duplicate]
  10. How to create materialized views in SQL Server?
  11. How can I clear the SQL Server query cache?
  12. SQL Server Random Sort
  13. SQL Server database backup restore on lower version
  14. Add a new table column to specific ordinal position in Microsoft SQL Server
  15. Is there a way to simplify a NULL compare of 2 values
  16. How to set monday as first day of week in SQL Server
  17. How do I flush the PRINT buffer in TSQL?
  18. SQL Server Login error: Login failed for user ‘NT AUTHORITY\SYSTEM’
  19. Drop primary key using script in SQL Server database [duplicate]
  20. OR Operator Short-circuit in SQL Server
  21. How to store directory / hierarchy / tree structure in the database?
  22. Which is quicker COALESCE OR ISNULL?
  23. Merge multiple rows into a single row
  24. How to set value to variable using ‘execute’ in t-sql?
  25. Data generators for SQL server? [closed]
  26. Taking the second last row with only one select in SQL Server?
  27. SQL Server Maximum rows that can be inserted in a single insert statment
  28. sql geography to dbgeography?
  29. How to Bulk Insert from XLSX file extension?
  30. SQL Server 2008 – order by strings with number numerically

Leave a Comment