How do you avoid double quotes inside the fulltext "contains" SQL function?

Question

How to avoid double quote character inside MS SQL 'contains' function?

SELECT decision FROM table WHERE CONTAINS(decision, '34" AND wide') 

Usually contains () expects double quotes to surround the exact phrase to match, but I want to find the actual double quote character. I tried to slip away with \, `and even another double quote, but none of this worked.

PS I understand that such a simple example can be done using the LIKE instruction, but I need to use the full-text search function. The query presented here has been simplified from my actual query, for example, for purposes.

Answer 1

From the documentation :

Punctuation is ignored. Therefore, CONTAINS(testing, "computer failure") corresponds to a line with the value: "Where is my computer? Failure to find it will be expensive."

Since FULLTEXT doesn't even index punctuation, you need to fine- FULLTEXT results with LIKE :

 SELECT decision FROM table WHERE CONTAINS(decision, '34 AND wide') AND decision LIKE '%34"%' 

This will preserve the benefits of the full text.

Answer 2

The above answer does not work in SQL Server 2008 if your string is for a single digit.

It works:

 SELECT partdescription FROM table WHERE CONTAINS(partdescription, '10') AND decision LIKE '%10"%' 

This does not work:

 SELECT partdescription FROM table WHERE CONTAINS(partdescription, '6') AND decision LIKE '%6"%'' 

EDIT: An explanation of why a single digit cannot be indexed.

If the SYSTEM stop word list (or noise word) is used, it contains each of the numeric digits as words that should be ignored. You can change the contents of the Stop Word list or disable Stop Words altogether.

  ALTER FULLTEXT INDEX ON dbo.my_ft_table SET STOPLIST = OFF; 

After one of these changes is made, the text must be reindexed before the individual digits become searchable. After reindexing, it will really be possible to find "6".