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.

Answer 2

Quoting from the Microsoft reference on CREATE FULLTEXT INDEX:

For non-BLOB and non-XML columns containing text data in multiple languages, or for cases when the language of the text stored in the column is unknown, it might be appropriate for you to use the neutral (0x0) language resource. However, first you should understand the possible consequences of using the neutral (0x0) language resource. For information about the possible solutions and consequences of using the neutral (0x0) language resource, see Best Practices for Choosing a Language When Creating a Full-Text Index.