How do you escape double quotes inside a SQL fulltext 'contains' function?

Question

How do you escape a double quote character inside a MS SQL 'contains' function?

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

Normally contains() expects double quotes to surround an exact phrase to match, but I want to search for an actual double quote character. I've tried escaping it with \, `, and even another double quote, but none of that has worked.

P.S. I realize a simple example like this could also be done using the LIKE statement, but I need to use the fulltext search function. The query I provided here has been simplified from my actual query for example purposes.

Solution 1

From documentation:

Punctuation is ignored. Therefore, CONTAINS(testing, "computer failure") matches a row with the value, "Where is my computer? Failure to find it would be expensive."

Since FULLTEXT does not even index the punctuation, you'll need to fine-filter your results using LIKE:

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

This will preserve the benefits of fulltext.

Solution 2

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

This works:

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

This doesn't work:

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

EDIT: Explanation of why a single digit may not be indexed.

If the SYSTEM stop word (or noise word) list is being used it contains each of the numeric digits as words to be ignored. You can change the contents of the Stop Word list or you can turn off the Stop Words altogether.

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

Once one of these changes is made, the text affected must be reindexed before the single digits become searchable. After the reindexing it will indeed be possible to search for '6'.

Comments

[RLF]

I updated the explanation to include why a single digit will or will not be indexed.