Can i make a table/column like this:

  • Accent sensitive for Turkish and English.

  • Accent insensitive for Arabic.

Actually if this can be done on a full-text index, that would also work for me.

Accent does matter for Turkish but doesn't matter that much for Arabic.

And I don't want to compose individual tables for each table if possible.


No, this is not how Collations work. Not only are the various sensitivities (case, accent, width, and Kana) not per Language, you can't even get more than one Language at a time per column or expression. The issue is that multiple languages may share one or more characters. Even if the languages you are concerned about using have entirely distinct characters (e.g. Arabic vs English), you can only specify one locale at a time (even at the OS level and other software -- I've never seen anything allow for multiple locales in a single column / entity / object).

I would suggest picking the Collation that will be used most often as how the column is defined, and then for the other one or two you can create computed columns, defined as simply being the column you are dealing with here -- but with the appropriate Collation -- and defining the Full Text indexes on the various columns. For example:

  Col1 NVARCHAR(50) COLLATE Latin1_General_100_CI_AS_SC,
  Col2 AS [Col1] COLLATE Turkish_100_CI_AS_SC [PERSISTED],
  Col3 AS [Col1] COLLATE Arabic_100_CI_AI_SC [PERSISTED]

I'm not sure if Full Text Search will require that the computed columns be defined as PERSISTED, so you might want to try with and without it to see not only what is allowed, but also which performs better. This approach will at least make it easy to keep the values in sync as they only truly exist in one place.

| improve this answer | |

As far as I know not. A not superb but working solution could be to split your labels in 2 fields, one sensitive and one not ... Then depending on the language search in the one or the other ... You can assign a different collation to each field.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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