How to sort Japanese character in Sql

Question

How to sort the next Japanese character on an Sql server,

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

higher order(1,3,2,4)

But, the actual order is (i.e. the conclusion I want)

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

higher order(1,2,3,4)

I tried like this:

 select * from test order by Test `COLLATE Japanese_CS_AS_KS` 

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

Answer 1

Looking at Windows Japanese mappings , not your SQL mappings (SQL Server supports both), 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. Also note that Japanese_XJIS_100_CS_AS_KS does not work

Answer 2

The sort code contains 3 particles at the end:

• CS - case sensitive
• AS - emphasis sensitive
• KS - sensitive to kanatype, which is related to Japanese (see this questions )

If you need sorting that is case insensitive, change CS to CI. Therefore, a different combination of sensitivity may be required.

Answer 3

In addition to @gbn answer

• 賃 has a radical touch 154.6
• 解 has 148.6
• 更 has 73.3

So the question should be soon, why do you want descending?