How to sort the Japanese Character in Sql

Question

How to sort the following Japanese Character in Sql Server,

賃貸人側連絡先 (Lessor side contact)
解約連絡先 (Termination contacts)
賃借人側連絡先 (Lessee side contact)
更新連絡先 (Update contact)

above order(1,3,2,4)

But, Actual order is ,(ie. output that i want)

賃貸人側連絡先 (Lessor side contact)
賃借人側連絡先 (Lessee side contact)
解約連絡先 (Termination contacts)
更新連絡先 (Update contact)

above order(1,2,3,4)

I tried like this,

select * from test order by Test `COLLATE Japanese_CS_AS_KS`

but order of result like (3,4,2,1)

Answer 1

Looking at Windows Japanese collations rather then your SQL collations (SQL Server supports both), by trial and error, this works

DECLARE @t TABLE (id int, SomeString nvarchar(100));
INSERT @t VALUES
(1, N'賃貸人側連絡先'),
(3, N'解約連絡先'),
(2, N'賃借人側連絡先'),
(4, N'更新連絡先');

select * from @t order by SomeString COLLATE Japanese_Bushu_Kakusu_100_CS_AS_KS desc

Not sure why you need DESC though. Also note Japanese_XJIS_100_CS_AS_KS does not work

Answer 2

The collation code has 3 particles at the end:

• CS - case sensitive
• AS - accent sensitive
• KS - kanatype sensitive which is smth Japanese related (discussed in this questions)

If you want a collation that is case insensitive- change CS to CI. So perhaps a different combination of sensitivities is required.

Answer 3

In addition to @gbn's answer

• 賃 has Radical-Stroke Count 154.6
• 解 has 148.6
• 更 has 73.3

So the question should rather be, why do YOU want descending order?