May I know which collate supports the all languages data comparisons

Question

We are storing text in a column(nvarchar type), which contains data for all languages. We are applying charindex function on that column but it is providing invalid results for some of the languages. Below is the example, In which text is in 'Amharic' language. It is always returning 1. May I know which collation we can use that supports the all languages.

select charindex(N'ሳተላይት',N'ይት')

Answer 1

To view the available collations:

select *
from sys.fn_helpcollations()
order by name

Maybe you can apply this collation or workaround:

select charindex(N'ሳተላይት' collate Latin1_General_100_CI_AS, N'ይት' collate Latin1_General_100_CI_AS)
-- result: 0

select charindex( N'ይት' collate Latin1_General_100_CI_AS, N'ሳተላይት' collate Latin1_General_100_CI_AS)
-- result: 4

Answer 2

Depends on what you mean with "all languages". There is no collation to support all languages fully as this is an impossible task. Different languages have different rules that are in contradiction with each other. You cannot have a collation that sorts data both correctly according to Swedish and German at the same time. Both have the letters Ä and Ö, but they sort them differently. And Björn = Bjoern in German, but not in Swedish.

However, if you mean support for characters as such, it is important that you pick a collation with 100 in the name. If you pick a collation without a version number, this is a collation based on a very old version of the Unicode standard, and many code points are undefined, and this can have some funny effects. Check this:

select charindex(N'ይት' COLLATE Latin1_General_100_CI_AS, N'ሳተላይት') -- Returns 4
select charindex(N'ይት' COLLATE Latin1_General_CI_AS, N'ሳተላይት') -- Returns 1

(I swapped the strings in your example, since else it does not make sense. The string you are looking for comes first.

Answer 3

Hi @Manjunath Cycle

In addition to the above two answers, you can refer to the following link to see more information about Collation and Unicode support.

https://learn.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-ver16

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.