Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

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

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...