Change sorting during query in SQL Server? The correct grade for English / Cyrillic / EU

Question

I am researching for an existing system in which SQL Server is the backend. We are starting to store special header data in several languages ​​(including Russian in Cyrillic and other European languages). My question is sorting this data, what are my options for changing the sort order on demand without changing the sort? Can I solve the sorting issue in the request itself?

Here is an example of a query I'm playing with to watch:

SELECT STR FROM ( SELECT N'01' AS STR UNION SELECT N'02' AS STR UNION SELECT N'03' AS STR UNION SELECT N'04' AS STR UNION SELECT N'05' AS STR UNION SELECT N'06' AS STR UNION SELECT N'07' AS STR UNION SELECT N'08' AS STR UNION SELECT N'09' AS STR UNION SELECT N'10' AS STR UNION SELECT N'11' AS STR UNION SELECT N'12' AS STR UNION SELECT N'13' AS STR UNION SELECT N'14' AS STR UNION SELECT N'15' AS STR UNION SELECT N'16' AS STR UNION SELECT N'17' AS STR UNION SELECT N'18' AS STR UNION SELECT N'19' AS STR UNION SELECT N'20' AS STR UNION SELECT N'21' AS STR UNION SELECT N'22' AS STR UNION SELECT N'23' AS STR UNION SELECT N'24' AS STR UNION SELECT N'25' AS STR UNION SELECT N'26' AS STR UNION SELECT N'27' AS STR UNION SELECT N'28' AS STR UNION SELECT N'29' AS STR UNION SELECT N'30' AS STR UNION SELECT N'31' AS STR UNION SELECT N'32' AS STR UNION SELECT N'33' AS STR UNION SELECT N'a34' AS STR UNION SELECT N'b35' AS STR UNION SELECT N'c36' AS STR UNION SELECT N'd37' AS STR UNION SELECT N'e38' AS STR UNION SELECT N'f39' AS STR UNION SELECT N'g40' AS STR UNION SELECT N'h41' AS STR UNION SELECT N'i42' AS STR UNION SELECT N'j43' AS STR UNION SELECT N'k44' AS STR UNION SELECT N'l45' AS STR UNION SELECT N'm46' AS STR UNION SELECT N'n47' AS STR UNION SELECT N'o48' AS STR UNION SELECT N'p49' AS STR UNION SELECT N'q50' AS STR UNION SELECT N'r51' AS STR UNION SELECT N's52' AS STR UNION SELECT N't53' AS STR UNION SELECT N'u54' AS STR UNION SELECT N'v55' AS STR UNION SELECT N'w56' AS STR UNION SELECT N'x57' AS STR UNION SELECT N'y58' AS STR UNION SELECT N'z59' AS STR UNION SELECT N'A60' AS STR UNION SELECT N'B70' AS STR UNION SELECT N'C71' AS STR UNION SELECT N'D72' AS STR UNION SELECT N'E73' AS STR UNION SELECT N'F74' AS STR UNION SELECT N'G75' AS STR UNION SELECT N'H76' AS STR UNION SELECT N'I77' AS STR UNION SELECT N'J78' AS STR UNION SELECT N'K79' AS STR UNION SELECT N'L80' AS STR UNION SELECT N'M81' AS STR UNION SELECT N'N82' AS STR UNION SELECT N'O83' AS STR UNION SELECT N'P84' AS STR UNION SELECT N'Q85' AS STR UNION SELECT N'R86' AS STR UNION SELECT N'S87' AS STR UNION SELECT N'T88' AS STR UNION SELECT N'U89' AS STR UNION SELECT N'V90' AS STR UNION SELECT N'W91' AS STR UNION SELECT N'X92' AS STR UNION SELECT N'Y93' AS STR UNION SELECT N'Z94' AS STR ) AS SUBTABLE ORDER BY STR ASC 

I get the following:

 a34 A60 b35 B70 c36 C71 d37 D72 e38 E73 f39 F74 g40 G75 h41 H76 i42 I77 j43 J78 k44 K79 l45 L80 m46 M81 n47 N82 o48 O83 p49 P84 q50 Q85 r51 R86 s52 S87 t53 T88 u54 U89 v55 V90 w56 W91 x57 X92 y58 Y93 z59 Z94 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 

It seems that some of the Cyrillic characters are similar to their English equivalents, are there any ways to have these alternations as part of “A to Z”?

What other collation options are available in SQL Server?

If I cannot do this in the query, then what is the most reliable sorting selected based on the column?

Answer 1

You can choose the Cyrillic alphabet, for example:

 order by STR collate Cyrillic_General_CI_AI 

A complete list of sorts can be found by calling:

 select * from ::fn_helpcollations() 

CS stands for "Case Sensitivity," "AI" stands for "Emphasis Excluding."