14

There seems to be something special about 'tR' in Vietnamese collation. Appreicate if anyone who knows about it can explain in simple terms. This issue was discovered during the installation of our product on a "Vietnamese" collated SQL Server. One of the tables in the schema has 'tR' in it's name but a stored procedure is referencing the table in all lower case 'tr'. And this reference fails.

I guess this situation is analogous to '阝' matching 'ss' in other collations.

Here is a reproduction:

select  case when 'tr' = 'tR' COLLATE SQL_Latin1_General_CP1_CI_AS   then 'match' else 'no match' end 
select  case when 'tr' = 'tR' COLLATE Vietnamese_CI_AI   then 'match' else 'no match' end 
select  case when 'tr' = 'TR' COLLATE Vietnamese_CI_AI   then 'match' else 'no match' end 

Results:

-----
match


--------
no match


-----
match

The second T-SQL produces the mismatch. Other combinations of 't' and 'R' do not.

31

Given that this behavior is present in the newer version of that collation, and that combinations such as "fr" and "fR" do match (as expected), it could only be culture-specific linguistic rules for that combination of characters.

SELECT CASE WHEN 'tr' = 'tR' COLLATE Vietnamese_100_CI_AI THEN 'Y' ELSE 'N' END;
-- N

SELECT CASE WHEN 'fr' = 'fR' COLLATE Vietnamese_100_CI_AI THEN 'Y' ELSE 'N' END;
-- Y

I found the rule in the sort weight file **. It's the combination of "tr" that's special (in Vietnamese), not "tR". It seems that Vietnamese has certain letter combinations that combine to form a single character, such as the "CH" and "LL" combinations in Spanish. So, the following are valid combinations for the "character" that "T" + "R" combine to form in Vietnamese:

  1. tr
  2. Tr
  3. TR

The combination of "tR" does not form the "TR" character, most likely because that is an unnatural capitalization that more so implies separation of words, such as with Pascal / Camel -casing (e.g. "ChatRoom" and "chatRoom", respectively, as opposed to "tRogdor the Burninator") (educated guess on my part).

The following example shows that the "tr" combination sorts after "tz":

SELECT *
FROM   (VALUES (N'Atra'), (N'Atz'), (N'Aua'), (N'Ata'), (N'AtR')) tmp(col)
ORDER BY tmp.[col] COLLATE Vietnamese_100_CI_AI ASC
/*
Ata
AtR
Atz
Atra
Aua
*/

Those results are due to "tr" combining to form a single character that naturally sorts after "t". Meaning, the sorting algorithm sees the following:

 Character #
1  |  2   | 3
-------------
A  |  t   | a
A  |  t   | R
A  |  t   | z
A  |  tr  | a
A  |  u   | a

Vietnamese has other two-letter combinations that work the same way as "TR" (i.e. case-insensitive: tr == Tr == TR <> tR ):

  1. CH
  2. GI
  3. KH
  4. NG
  5. NH
  6. PH
  7. QU
  8. TH

For more info on working with collations / encodings / Unicode, please visit my site: Collations Info


** A sort weight file contains code points and their respective weight values which are separated into categories such as diacritic weight, case weight, etc. There are usually sections that describe transformations, such a combining two code points to be a single weight for a particular culture/locale (e.g. Vietnamese). There can be mappings for decomposing pre-composed characters into individual characters, etc. Please see: Accessing the Windows Sorting Weight Table.

NOTE: Microsoft provides several sort weight files since they have been updated over the years as new versions of Windows and Windows Server have been released. Please keep in mind that none of those files is a 100% match for the rules that SQL Server uses. The closest we get is the Windows Server 2008 Sorting Weight Table.txt file, which should closely match the behavior of the version 100 collations (i.e. those with _100_ in their names).

4
  • 1
    There are several character combinations that will behave the same, as they are treated special in the Vietnamese alphabet. Ch, Th, Ng, Tr, etc. All of these will require the same "awkward case sensitivity" when running under the Vietnamese collation.
    – AMtwo
    Aug 25 at 11:01
  • Is there a way to switch off this behavior of " combining multiple characters to form a single character, " ? I do understand that sorting and string comparisons could be a mess if I do choose this option, even if it exists. Short of fully i18n-ing our application, the quick fix is to change the lower case name of the table in the stored procedure to match exactly the table name as it is defined. But our re-releases process is cumbersome. Other Vietnamese collations seem to use have the same "sorting weight" definition
    – QFirstLast
    Aug 25 at 18:02
  • @QFirstLast The only ways to turn off linguistic rules are: 1) use a binary collation (i.e. name ending in _BIN2), or 2) use a collation for a different culture that doesn't have the undesirable linguistic rule(s). Option 1 won't work as it also won't equate tr and tR. So, either pick a different collation, or the best option really is to fix the code, or even the table name (unless this is a Camel/Pascal -case issue and the table name is correct). I completely understand wanting to avoid a messy patch process, but that's what's best, even if you don't need a Vietnamese collation. Aug 25 at 18:21
  • Thanks. I will go for a patch
    – QFirstLast
    Aug 27 at 5:03

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.