[RESOLVED] SQL Server '05: How to get fulltext search not to ignore hyphens

[aconybeare]

Hi,

I am using sql server 2005 fulltext search and would like to be able to search for scientific terms e.g. "5-HT"

Does anyone know how to tell sql server not to ignore the hypen?

Regards Allan

[riteshjain1982]

may be you should look at this MS KB

WORKAROUND
To work around this problem, avoid using special characters in thesaurus file terms.

STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

[aconybeare]

Riteshjain,

Thanks for your reply, I'm not using the thesaurus file but the article described similar symptoms to the one's I'm experiencing and it gave me an idea for a workaround.

The following is for anyone interested in my solution/hack -

I've created a new field called FTS_Data nvarchar(max) with a full-text catalog and added a trigger to the table which populates the FTS_Data field. I strip out any hyphens ("- " & "-"), this means the base data remains unadultered and the search data will return accurate results.

When constructing my search string using (in my case) containstable I look for and strip out "- " & "-"

...CONTAINSTABLE(mytable,(fts_data),'"5HT*"')...

Allan

[riteshjain1982]

glad to know your problem is solved and thanks for workaround.