8

Question to any Japanese reader:

When you look in SQL-Server and do

SELECT * FROM sys.fn_helpcollations() 
WHERE (1=1) 
--AND description LIKE '%insensitive%'
AND name LIKE '%japanese%' 

you get Kanatype-sensitive and Kanatype-insensitive collations.

My understanding is, that this is two different kind of alphabets, Hiragana and Katakana.

SQL-Server documentation states:

If this option is not selected, SQL Server considers Hiragana and Katakana characters to be equal for sorting purposes

So my question:

When/Why would you want to use kanatype-insensitive collation?
What's the deeper purpose?
Is a phonebook-lookup kanatype-sensitive or kanatype-insensitive?

In which cases would you use a sensitive collation, and when an insensitive collation?

10

In general, if you're storing any Japanese text that needs to be sorted, you probably want to go with Kanatype insensitive. Why would you want it like this? Because it makes sorting more intuitive in terms of Japanese language.

In english, since we have only one writing system, it's easy to sort things algorithmically. We simply order the characters by their character codes (already in alphabetical order) and we're done. In Japanese, though, because there are multiple ways to write out equivalent sounds, sorting can get a bit tricky. Hiragana and Katakana alphabets are separated into separate Unicode blocks, so when we try sorting things with "Kanatype sensitivity", we end up with results that aren't completely intuitive.

Imagine you had a list of names that you wanted to sort:

{ "ピカチュウ","さとし","マリオ","まちだ","はるか" }

The romanized equivalent to the list is:

{ "Pikachu","Satoshi","Mario","Machida","Haruka" }

When sorted kanatype sensitive, you would get the following result:

{ "さとし","はるか","まちだ","ピカチュウ","マリオ" }
{ "Satoshi","Haruka","Machida","Pikachu","Mario" }

When sorted kanatype insensitive, you would get this result instead:

{ "さとし","はるか","ピカチュウ","まちだ","マリオ" }
{ "Satoshi","Haruka","Pikachu","Machida","Mario" }

To Japanese speakers, the second sort is a lot more intuitive, as the results are actually sorted phonetically instead of based on character sets. "まちだ" and "マリオ" both start with the same phonetic sound, but because one uses hiragana "ma" and the other uses katakana "ma", they are separated when kanatype sensitivity is enabled. With kanatype insensitivity, the list can be properly sorted so that the two words appear next to each other on the list despite their writing system differences.

A good analogy for English language would be case-sensitivity. Imagine if you wanted to sort a list of words for a dictionary, some of them proper nouns while others are not:

{"New York","new","jet","Japan","squirm","SQL"}

If we ignored the fact that uppercase and lowercase letters represent the same letter and just sort based on character code, we would get something like this:

{"Japan", "New York", "SQL", "jet", "new", "squirm"}

A dictionary sorted like this would hardly be useful, especially if we wanted to look up a word without knowing whether it started with an uppercase or lowercase letter. We'd have to check the first part of the dictionary with all the proper nouns before checking the last part with all other words.

If we ran a case insensitive sort that treat "A" and "a" as the same letter despite having separate character codes. We would get a result that is much more intuitive:

{"Japan","jet","new","New York","squirm","SQL"}

So in general, unless you have a specific reason not to, you should always disable kanatype sensitivity. A phonebook-lookup would be kanatype sensitive. Note that in Japanese there is also an additional character type, Kanji, that you would also need to work with. Kanji is much harder to sort, as there are almost always multiple ways to read each Kanji and no real "alphabetical" order to the Kanji. Most forms intended for Japanese people usually have two fields for names: the user's name as it is normally written out, and the user's name completely written out in katakana. Not only does this let people know how to correctly pronounce a name which might be ambiguous written solely in Kanji, but it allows software to sort by the unambiguous katakana-only field, making the sort kanatype insensitive.

For more information, I definitely recommend checking out this excellent article, which explains the issues with sorting in Japanese much better than I can.

TL;DR: Kanatype insensitivity makes sorting Japanese text more intuitive and should generally always be enabled unless you have a reason not to.

  • 1
    I believe you've got the terms wrong way around: insensitive = ま and マ together; sensitive = ま and マ separate. Case insensitive = C and c together, sensitive = C and c separate. – oals Dec 3 '15 at 8:06
  • @oals You're right. I thought it seemed wrong, because I double checked the documentation pages, but I must have read them wrong twice! Thanks. – Mewy Dec 3 '15 at 15:13
  • As you wrote, we cannot sort (say) names without knowing yomi. Unless some yomi are stored in hiragana and the others are in katakana, sorting yomi kana-type-insensitively is not useful. So while I think that kana-type insensitive sort was supposed to help in the situation you described, I doubt that it is really useful. – Tsuyoshi Ito Dec 6 '15 at 0:04
  • Kanatype-insensitive sorting assumes that the person is knowledgeable of the term they are searching. It is also intuitive when thinking of phonetic sounds (think Karaoke song lists!). Kanatype-sensitive sorting is more useful when wanting to determine whether the term that is being used is a native term or a non-native term (think textbooks and dictionaries!) – psosuna May 31 '17 at 1:00
  • Collation in English puts Japan and jet next to each other regardless of whether the collation is case sensitive. – Edward Brey Oct 18 '18 at 20:29

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.