Solved

Determining details of sort order in a collation

Posted on 2006-11-10
7
840 Views
Last Modified: 2012-08-13
I'm using collation Finnish_Swedish_CI_AS in a database.

I note that (for instance) V and W are considered equal in this sorting, which surprises me (and possibly all other swedish people, at least the computer-firendly ones:-)
Also, I note that the sorting of danish Ö (o-umlaut) is not sorted together with swedish Ö -- etc etc. Also, there is (of course) a specific sort order of all (as an example) accented "A" characters, and I'd like to know exactly which order it is.

I've tested a little by creating a table with all possible character values, and then checked the sorted output.
However, is there any way to exactly determine the specific details of a collation sort order?
Something like "help_collation 'collation_name'" or "SELECT * FROM ::fn_helpcollations_details()" or something similar?
What I'm looking for is a way to explain to people exactly how each character is sorted.

Also, SQL Server is using Windows collation, right? Perhaps one could get this kind of list somewhere from Windows instead?

Thanks,
/Stefan Lennerbrant
0
Comment
Question by:stefanlennerbrant
  • 4
  • 3
7 Comments
 
LVL 11

Expert Comment

by:regbes
ID: 17913470
Hi stefanlennerbrant,

It appears that SQL gets the order from Windows

http://msdn2.microsoft.com/en-us/library/ms144260.aspx

HTH

R.
0
 

Author Comment

by:stefanlennerbrant
ID: 17913645
Thanks.
This page seems to describe "SQL Collations" in general (compared to "Windows collations"). The SQL Collation is depreciated, aren't they, and kept for backwards compatibility reasons?

The sort order used by me is (I think?) a Windows collation. It's named "Finnish_Swedish_CI_AS" and described is by sp_helpsort as:
Finnish-Swedish, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 60 on Code Page 850 for non-Unicode Data

However, the question still remains: How to get a detail description of the actual sort order used?
Something like a list where it is specified that A is listed before B, I before Í, V and W treated as equal, hyphen "-" is ignored, etc etc

Possibly the hint "SQL Server Sort Order 60 on CP850" above is something to investigate? I'll check into it.
Hasn't anybody ever been curious regarding the actial detail sorting order in a collation setup? :-)

/Stefan Lennerbrant
0
 
LVL 11

Expert Comment

by:regbes
ID: 17913670
stefanlennerbrant,


this should di it then

http://msdn2.microsoft.com/en-US/library/ms143515.aspx
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:stefanlennerbrant
ID: 17913686
Well, that just says things like:
  Distinguishes between accented and unaccented characters. For example, 'a' is not equal to 'ấ'.

I'm interested in _exactly_ which order a, á, à, â are sorted, and whether v and w are sorted in different order or are considered equal, etc etc

Nothing is said about that, or did I miss something?

/Stefan Lennerbrant
0
 
LVL 11

Accepted Solution

by:
regbes earned 125 total points
ID: 17913865
stefanlennerbrant,

yes i did not see which accent is sorted before another, its an intresting question but does not realy affect me as we do not user accents here
you could try something like this to get an indirect answer to you question


declare @i int
set @i = 0
create table #ChrOrd (num int,Chr char(1)COLLATE SQL_Latin1_General_CP1_CS_AS ,Chr2 char(1)COLLATE Finnish_Swedish_CI_AI)
set @i = 10
while @i<255

begin
insert into #ChrOrd select @i, char(@i),char(@i)

set @i = @i+1
end

select * from #ChrOrd order by 1
select * from #ChrOrd order by 2

drop table #ChrOrd



 
0
 

Author Comment

by:stefanlennerbrant
ID: 17934462
I don't find any theoretical explanation (or specification) on why the sort order is as it is, but your example does show it empirically. It's the same type of test that I already did, but built in a "nicer way".
However, I'd rather define the fields as char(2) instead and then insert two copies of each:
  insert into #ChrOrd select @i, char(@i)+'a',char(@i)+'a'
  insert into #ChrOrd select @i, char(@i)+'z',char(@i)+'z'

and then display them as (which I suppose you meant from the beginning)
  select * from #ChrOrd order by 2
  select * from #ChrOrd order by 3

Then you clearly see that v+w are considered equal, etc etc.

Well - now if I don't like the defined sort order? Is there anything I can do about it? (apart from trying to find a collation that sorts things in a "better way" according to my needs)
I'll create a new question for this:-)
0
 
LVL 11

Expert Comment

by:regbes
ID: 17936196
stefanlennerbrant,

I think you are lucky MS even admits that accents are diffrent to each other and gives them diffrent sort order, however the cahnce of them getting it right was slim :)
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS SQL Backup 24 70
Distinct values from two tables 14 18
Azure SQL DB? 3 16
Convert SP in a format for debugging 7 0
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now