FULLTEXT search with a multi-language column

Question

Is there a way to use FULLTEXT in a multi-language table without giving each language its own column?

I have one column I need to search, but the language in that column varies:

ProductID    int
Description  nvarchar(max)
Language     char(2)

Language can be one of: en, de, it, kr, th

Currently I build a concordance and use that for searching. But this is only for English, German and Italian, and even for those it doesn't support stemming. Everything else uses LIKE '%searchterm%', and I'm trying to improve on that.

I'm using SQL Server 2005.

Answer 1

Instead of a separate column per language, if you know which rows contain which language you could create an indexed view filtered to include only rows of a single langauge per language and FTI each of those. You'll need to query each view individually though.