How to full text search worked with special character like "of"?

Question

How to search with fulltext search if keyword has special character like

"of", "-" ...etc

select * from table1
join CONTAINSTABLE (table1,(Title,Description_HTML),
'"of" and "Department"') 
tb2 on tb2.[key] = table1.ID

I am unable to view the result?

Answer 1

As Martin Smith commented, "It depends." The factors that control the processing of Full Text indexes depends on the configuration you use in your CREATE FULLTEXT INDEX command.

• Language - Which controls the parsing of words and phrases according to that language's rules.
• Stop Word List - Words to be left out of the search, though their position in the string is recognized.
• Accent Sensitivity - Determines whether accents are processed or ignored.

There is a tool to help you test your queries: https://msdn.microsoft.com/en-us/library/cc280463.aspx

You can use the sys.dm_fts_parser command to test your full text results. For example, three similar searches using this function.

select * from sys.dm_fts_parser('"of" and "-" and "Department"',
   1033, NULL, 0) --American English, No stop words, Accent Insensitive;

Returns 2 tokens: of, Department

select * from sys.dm_fts_parser('"of""-""Department"',
   1033, NULL, 0) --American English, No stop words, Accent Insensitive;

Returns 2 tokens: of, Department

select * from sys.dm_fts_parser('"of-Department"',
   1033, NULL, 0) --American English, No stop words, Accent Insensitive

Returns 3 tokens: of-Department, of, Department

In general a "-" is ignored when standing alone. However, depending on the Language choice in your Full Text Index, the "-" serves as a hyphen in text and may be reported as shown in the third example which includes "of-Department" as one of the tokens returned.