Full-Text: How to determine valid LCIDs for CONTAINS() LANGUAGE parameter?
I need to determine the set of valid LCIDs that can be passed as values to the LANGUAGE option of the CONTAINS() clause.
I can get the initial set (17) from the sys.fulltext_languages table. However, certain sub-languages of these languages are also supported. For example, LCIDs 12, 2060, 3084, 4108, 5132, 6156 are also supported as sub-languages of 1036 French (France).
Normally, the determination can be made by matching on the primary language ID portion (bits) of the LCID. E.g., decimal 12 is the primary language for French.
The problem, in SQL Server 2005, is with Spanish, and possibly others. Spanish (Spain - Modern Sort) (LCID 3082) is in sys.fulltext_languages. However, Spanish (Puerto Rico), (Mexico), and others (e.g., LCIDs 20490, 2058, 1034, 10, etc.) which all have decimal 10 as the primary language fail with the following error:
SELECT content_idFROMcontent WHERECONTAINS(short_description,'FORMSOF (INFLECTIONAL, trabajar)',LANGUAGE 10;
Msg 7696, Level 16, State 10, Line 1
Invalid locale ID was specified. Please verify that the locale ID is correct and corresponding language resource has been installed.
How can I obtain the valid list (by querying information within SQL Server 2005) without having to resort to trial-and-error?
Application background
Our application allows users to store data identified by a language (of the 200+ supported LCIDs by Microsoft). We need to 1.) identify which languages andsub-languages (LCIDs) can be used for full-text queries and 2.) our code uses this knowledge to provide the proper LCID code to the LANGUAGE parameter on the CONTAINS() clause.
