Sql Server 2005 Fulltext case sensitivity problem

Question

I seem to have a weird bug in Microsoft SQL Server 2005 where FREETEXT() searches are somewhat case-sensitive despite the collation being case-insensitive (Latin1_General_CI_AS).

First of, LIKE queries are perfectly case-insensitive, so

WHERE column LIKE '%word%'

and

WHERE column LIKE '%Word%'

return the same results.

Also, FREETEXT are infact case-insensitive to some extent, for instance

WHERE FREETEXT(column, 'Word')

will return results with different cases.

BUT

WHERE FREETEXT(column, 'word')

while still returning case-insensitive matches for word, gives a different resultset. Or, as I found out after some investigation, searching for word gives all matches for different cases of word but searching for Word gives the same PLUS inflectional results.

Or to use one of the actual cases I found, searching for marketingleader returns all results containing that word, independent of the case, whereas searching for Marketingleader would return those, but also results that just contain leader that don't show up when searching for the lower case.

has anyone got any Idea as to what is causing this and how I could turn on inflectional/fuzzy searching for lower-case words as well?

Any help would be appreciated.

Answer 1

Use the alternative to freetext which is contains and the inflectional results are optional ..

CONTAINS (Transact-SQL)

.. oups just saw that you mention contains in your question, but does it behave the same way as the freetext in the provided examples ?