Search code examples
sql-serverfreetextfreetexttable

How do I get synonyms in SQL Server FreeTextTable() searches?


I did a search for "GLBT" where the text in the DB has "LGBT+" in it. It did not return any rows. Same for doing a search on "cars" when the DB has "vehicles" in it.

Isn't FreeTextTable supposed to find synonyms? Or do I need to do something additional for it to find matches based on synonyms?


Solution

  • By default, SQL Server supports stemmers - different forms of a verb or single/plural of a noun. It does not (and can't!) natively know that car is a type of vehicle, for example, or that one acronym is a synonym for another.

    Ages ago, Microsoft promised a more comprehensive thesaurus file, but I don't think they have ever delivered one.

    You can try out some from 3rd parties, like this one. I think it would be a gigantic effort to create some all-encompassing English thesaurus that suits everyone, because things that are synonyms for your application (or even for different FT indexes on the same instance, since they share a single thesaurus) might be very different from, say, mine. I might prioritize java -> coffee, for example, while you might prioritize java -> jdbc.