sort utf string in sql server with join clause

Question

I have the following sql code which works fine but does not sort Party.Name which is non ASCII string:

Select  Party.Name 
FROM B_Customer  
Inner Join dbo.AccDomain On AccDomain.AccID = B_Customer.AccID  
Inner Join Party On PartyID = AccDomain.DomainID    
ORDER BY Party.Name  COLLATE Frisian_100_CI_AI

Also please note that the following code sorts appropriately:

select name  from Party where TypeID=10 order by name COLLATE Frisian_100_CI_AI

What should I do to for my sorting problem?

I am not able to reproduce the issue. Can you please provide sample data or create a fiddle for me to test it? — SouravA, Dec 30 '14 at 13:06

score 0 · Answer 1 · answered Dec 29 '14 at 13:31

0

Try this

   Select  p.Name FROM B_Customer b,Party p,AccDomain a
   where a.AccID = b.AccID  and
   p.PartyID = a.DomainID
   ORDER BY p.Name  COLLATE Frisian_100_CI_AI

If any problem with this answer must comment me....:)

answered Dec 29 '14 at 13:31

Virbhadrasinh

529
6
19

Thank you for your response but you changed my join clause which is not desireable for me and also it does not work ;) – Soosh Dec 29 '14 at 13:36

score 0 · Answer 2 · answered Dec 29 '14 at 15:00

0

How about this?

Select  Party.Name COLLATE Frisian_100_CI_AI
FROM B_Customer  
Inner Join dbo.AccDomain On AccDomain.AccID = B_Customer.AccID  
Inner Join Party On PartyID = AccDomain.DomainID  COLLATE Frisian_100_CI_AI  
ORDER BY Party.Name  COLLATE Frisian_100_CI_AI

answered Dec 29 '14 at 15:00

SouravA

5,147
2
24
49

Sorry man t does not work! Besides it gives me the following error: `Expression type uniqueidentifier is invalid for COLLATE clause` – Soosh Dec 30 '14 at 06:18
That's because only character data types can be collated. – SouravA Dec 30 '14 at 11:55

score 0 · Accepted Answer · answered Dec 31 '14 at 21:16

I found the problem! The problem was in my stored data, the string data in my table is in "Persian" language but some of the characters are "Arabic" which look almost exactly like Persian :

Persian : ی , Arabic :ئ 
Persian : ک , Arabic :ك

so Sql-Server couldn't understand what language I'm using :) I guess that some users may used Arabic keyboard to enter data, so I need to replace those characters before adding them into the database.

sort utf string in sql server with join clause

3 Answers3