Search a full text index for 'c#'

Question

Hi

I have a table (lets say it has one column called 'colLanguage') that contains a list of skills and has a full text index defined on it. One of the entries in the table is 'c#' but when I search for 'c#' (using the following SQL) I get no results back.

select * from FREETEXTTABLE(tblList, colLanguage, 'c#')

Can anyone help?

Thanks K

Answer 1

As far as I understand it, # is registered as a "word breaker" and so is not added to the index. You might be able to deregister it from the list of word breakers as described on this page:

http://msdn.microsoft.com/en-us/library/dd207002.aspx

Answer 2

My final solution is based on the fact that 'C#' is treated differently to 'c#'. When the letter before the '#' character is upper case the '#' is NOT treated as a word breaking character. Therefore I had to write a script update the table the full-text index was on to change all 'c#' into 'C#' (I then run this script as a sql job to ensure there isn't any lover case c#) and ensure when searching that 'c#' is changed into 'C#' which I did in my code.

Alternately if you have SQL2008 and can change the full-text language for the fts index you can set it to use the 'Neutral' language as the word breaker for the indexed columns. The 'Neutral' word breaker only breaks on white text. Then change your fts command to specify the that as the language used e.g. select * from FREETEXTTABLE(tblList, colLanguage, 'c#', LANGUAGE 0) you can seach for 'c#' or 'C#' with no other changes required* apart from rebuilding you indexes.

*you might need to change the 'default full-text Language' for your database instance, I had to.