2

I have an articles table as follows.

+-----------+--------------+---------+
| uid       | int          | PRIMARY |
| articleId | int          | UNIQUE  |
| lang      | varchar(2)   | UNIQUE  |
| title     | varchar(500) |         |
| body      | varchar(max) |         |
+-----------+--------------+---------+

I would like to implement full-text search on the article title and body. The complication is that the title and body can be in one of six languages, indicated by the "lang" column.

I see there are noise word files for several languages, so depending on the currently set language, I would like to use the appropriate noise file when searching.

Does this require one catalog per language? If separate catalogs are used, is it possible to, for example, not include French articles in the English catalog?

Improve this question
5
  • Seems that since, for a query, you'll always know the language you're after in advance, this might be a case where splitting it into multiple tables makes sense. I'm not sure about all of the intricacies of Full-Text search to know if what you want is possible and, if so, how difficult it will be; but it may be worth considering trading the maintenance of multiple tables for the ease of querying them.
    – Aaron Bertrand
    Commented Aug 14, 2012 at 13:28
  • Excellent suggestion, however I'm dealing with the limitations of a third-party CMS. Reworking the schema is unfortunately not a possibility.
    – dangowans
    Commented Aug 14, 2012 at 13:46
  • Does the CMS just do normal DML? You could certainly place your actual data in tables, then create a view with the original table name that the CMS would interact with (and instead of triggers could help in directing content to the correct underlying table).
    – Aaron Bertrand
    Commented Aug 14, 2012 at 13:49
  • That could work. I fear that future updates and upgrades to the CMS would be potentially harder, especially if changes are made to the articles table. Great suggestion for an in-house system though.
    – dangowans
    Commented Aug 14, 2012 at 14:06
  • Well I figured it may come with some inherent limitations, which is why I posted it as a comment, not an answer. If it turns out you can use this approach, let me know, I can definitely expand on some thoughts.
    – Aaron Bertrand
    Commented Aug 14, 2012 at 14:14

1 Answer 1

Reset to default
0

When creating the index, it is possible to specify a "LANGUAGE" argument. Within the description of the "LANGUAGE" argument is the following:

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, use the neutral (0x0) language resource.

Then, when doing a search using FREETEXT or FREETEXTTABLE, once again use the "LANGUAGE" argument. Microsoft defines the "LANGUAGE" argument as follows:

Is the language whose resources will be used for wordbreaking, stemming, and thesaurus and noise-word removal as part of the FREETEXT query. This parameter is optional and can be specified as a string, integer, or hexadecimal value corresponding to the locale identifier (LCID) of a language. If language_term is specified, the language it represents will be applied to all elements of the search condition. If no value is specified, the column full-text language is used.

So, I will use one catalog and one index on the multi-lingual table using a neutral language resource. Then I'll use the appropriate language setting when searching so everything is parsed correctly.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.