1

I am taking a user search expression and converting it to a format understood by SQL Server's full-text search.

I have a question about what characters I must strip from the user's search text.

I'm thinking I need to double up both single and double quotes. Is there other punctuation I need to remove? Has anyone seen a complete list of invalid characters for this?

I looked at the MS documentation for CONTAINS and FORMSOF but didn't see where this issue is addressed.

Jonathan Wood
  • 65,341
  • 71
  • 269
  • 466

1 Answers1

0

Quite similar to this, though not duplicate, but start here

SQL Server Full Text Search Escape Characters?

In short, the SQL Server FTE is for natural language searches, so any forms of special characters will not feature in its index. You can almost strip off anything that is not of the normal character range.

This is an article about quotation marks in FTE searches, and shows most of the common search forms. http://support.microsoft.com/default.aspx?scid=kb;EN-US;246800

Replace all double quotes (clears the text and any improper quotations)   
   If the text string contains one of the key words "NEAR", "FORMSOF", or    
   "ISABOUT", the parsing is complete   
Else
      Surround any instances of 'and' or 'and not' with quotes
      Surround any instances of 'or' or 'or not' with quotes
      Surround the entire string with quotes
Community
  • 1
  • 1
RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
  • The problem is I was originally just stripping out most forms of punctuation. But now I'd like to be able to search for strings like C# and C++. Of course, there's also the question of if these are even indexed. But it seems like SQL Server finds them if I search on them. – Jonathan Wood Jan 24 '11 at 16:14