代码之家 › 专栏 › 技术社区 › DanP

javascript排序以匹配SQL Server排序

sorting unicode sql-server javascript

DanP · 技术社区 · 14 年前

有人能用JavaScript为我指出一种排序算法吗?它的排序方式与SQL Server相同(对于nvarchar/unicode列)?

作为参考,我之前关于这种行为的问题可以在这里找到: SQL Server 2008 - different sort orders on VARCHAR vs NVARCHAR values

与其尝试在服务器端更改排序行为,还有什么方法可以在客户机端匹配它吗?我之前的问题专门讨论了按排序顺序排列的破折号,但我认为这比简单地忽略破折号作为排序的一部分要多得多。

我在这里添加了一些额外的用例来更好地演示这个问题。

从SQL Server(2008)中排序的示例数据:

?test  
^&$Grails Found  
bags of Garbage  
Brochures distributed  
Calls Received  
exhibit visitors  
Exhibit Visitors  
-Exhibit Visitors  
--Exhibit Visitors  
Ãxhibit Visitors  
Grails Found

如何让javascript以相同的方式对相同的值进行排序?

如果我能进一步澄清,请告诉我。

3 回复 | 直到 6 年前

Brock Adams 6 年前

首先,您的数据库排序规则是什么?我想是 SQL_Latin1_General_CP1_CS_AS 或 SQL_Latin1_General_CP1_CI_AS . 如果是这样,那么下面的应该可以工作(还没有完全测试)。

看起来像是在写真 Unicode分拣机是一项重要的工作。我见过比规范更简单的税务代码。;-)它似乎总是涉及到查找表和至少三级排序——通过修改字符和收缩来解释。

我将以下内容限制在 Latin 1 , Latin Extended-A 和 Latin Extended-B 表/排序规则。算法应该可以很好地处理这些集合,但是我没有完全测试它,也没有正确地考虑修改字符(为了节省速度和复杂性)。

看到它 in action at jsbin.com .

功能:

function bIgnoreForPrimarySort (iCharCode)
{
    /*--- A bunch of characters get ignored for the primary sort weight.
        The most important ones are the hyphen and apostrophe characters.
        A bunch of control characters and a couple of odds and ends, make up
        the rest.
    */
    if (iCharCode < 9)                                                  return true;

    if (iCharCode >= 14   &&  iCharCode <= 31)                          return true;

    if (iCharCode >= 127  &&  iCharCode <= 159)                         return true;

    if (iCharCode == 39   ||  iCharCode == 45  ||  iCharCode == 173)    return true;

    return false;
}


function SortByRoughSQL_Latin1_General_CP1_CS_AS (sA, sB)
{
    /*--- This Sorts Latin1 and extended Latin1 unicode with an approximation
        of SQL's SQL_Latin1_General_CP1_CS_AS collation.
        Certain modifying characters or contractions my be off (not tested), we trade-off
        perfect accuracy for speed and relative simplicity.

        True unicode sorting is devilishly complex and we're not getting paid enough to
        fully implement it in Javascript.  ;-)

        It looks like a definative sort would require painstaking exegesis of documents
        such as: http://unicode.org/reports/tr10/
    */
    //--- This is the master lookup table for Latin1 code-points.  Here through the extended set \u02AF
    //--- Make this static?
    var aSortOrder  = [
                     -1,  151,  152,  153,  154,  155,  156,  157,  158,    2,    3,    4,    5,    6,  159,  160,  161,  162,  163,  164,
                    165,  166,  167,  168,  169,  170,  171,  172,  173,  174,  175,  176,    0,    7,    8,    9,   10,   11,   12,  210,
                     13,   14,   15,   41,   16,  211,   17,   18,   65,   69,   71,   74,   76,   77,   80,   81,   82,   83,   19,   20,
                     42,   43,   44,   21,   22,  214,  257,  266,  284,  308,  347,  352,  376,  387,  419,  427,  438,  459,  466,  486,
                    529,  534,  538,  559,  576,  595,  636,  641,  647,  650,  661,   23,   24,   25,   26,   27,   28,  213,  255,  265,
                    283,  307,  346,  350,  374,  385,  418,  426,  436,  458,  464,  485,  528,  533,  536,  558,  575,  594,  635,  640,
                    646,  648,  660,   29,   30,   31,   32,  177,  178,  179,  180,  181,  182,  183,  184,  185,  186,  187,  188,  189,
                    190,  191,  192,  193,  194,  195,  196,  197,  198,  199,  200,  201,  202,  203,  204,  205,  206,  207,  208,  209,
                      1,   33,   53,   54,   55,   56,   34,   57,   35,   58,  215,   46,   59,  212,   60,   36,   61,   45,   72,   75,
                     37,   62,   63,   64,   38,   70,  487,   47,   66,   67,   68,   39,  219,  217,  221,  231,  223,  233,  250,  276,
                    312,  310,  316,  318,  392,  390,  395,  397,  295,  472,  491,  489,  493,  503,  495,   48,  511,  599,  597,  601,
                    603,  652,  590,  573,  218,  216,  220,  230,  222,  232,  249,  275,  311,  309,  315,  317,  391,  389,  394,  396,
                    294,  471,  490,  488,  492,  502,  494,   49,  510,  598,  596,  600,  602,  651,  589,  655,  229,  228,  227,  226,
                    235,  234,  268,  267,  272,  271,  270,  269,  274,  273,  286,  285,  290,  287,  324,  323,  322,  321,  314,  313,
                    326,  325,  320,  319,  358,  357,  362,  361,  356,  355,  364,  363,  378,  377,  380,  379,  405,  404,  403,  402,
                    401,  400,  407,  406,  393,  388,  417,  416,  421,  420,  432,  431,  428,  440,  439,  447,  446,  444,  443,  442,
                    441,  450,  449,  468,  467,  474,  473,  470,  469,  477,  484,  483,  501,  500,  499,  498,  507,  506,  527,  526,
                    540,  539,  544,  543,  542,  541,  561,  560,  563,  562,  567,  566,  565,  564,  580,  579,  578,  577,  593,  592,
                    611,  610,  609,  608,  607,  606,  613,  612,  617,  616,  615,  614,  643,  642,  654,  653,  656,  663,  662,  665,
                    664,  667,  666,  574,  258,  260,  262,  261,  264,  263,  281,  278,  277,  304,  292,  289,  288,  297,  335,  337,
                    332,  348,  349,  369,  371,  382,  415,  409,  434,  433,  448,  451,  462,  476,  479,  509,  521,  520,  524,  523,
                    531,  530,  552,  572,  571,  569,  570,  583,  582,  581,  585,  632,  631,  634,  638,  658,  657,  669,  668,  673,
                    677,  676,  678,   73,   79,   78,  680,  644,   50,   51,   52,   40,  303,  302,  301,  457,  456,  455,  482,  481,
                    480,  225,  224,  399,  398,  497,  496,  605,  604,  626,  625,  620,  619,  624,  623,  622,  621,  334,  241,  240,
                    237,  236,  254,  253,  366,  365,  360,  359,  430,  429,  505,  504,  515,  514,  675,  674,  422,  300,  299,  298,
                    354,  353,   84,   85,   86,   87,  239,  238,  252,  251,  513,  512,  243,  242,  245,  244,  328,  327,  330,  329,
                    411,  410,  413,  412,  517,  516,  519,  518,  547,  546,  549,  548,  628,  627,  630,  629,   88,   89,   90,   91,
                     92,   93,   94,   95,   96,   97,   98,   99,  100,  101,  102,  103,  104,  105,  106,  107,  108,  109,  110,  111,
                    112,  113,  114,  115,  116,  117,  118,  119,  120,  121,  122,  123,  124,  125,  126,  127,  128,  129,  130,  131,
                    132,  133,  134,  135,  136,  137,  138,  139,  140,  141,  142,  143,  246,  247,  248,  259,  279,  280,  293,  291,
                    339,  336,  338,  331,  340,  341,  342,  423,  367,  373,  351,  370,  372,  383,  381,  384,  408,  414,  386,  445,
                    453,  452,  454,  461,  463,  460,  475,  478,  465,  508,  522,  525,  532,  550,  553,  554,  555,  545,  556,  557,
                    537,  551,  568,  333,  424,  343,  344,  586,  584,  618,  633,  637,  639,  645,  659,  649,  670,  671,  672,  679,
                    681,  682,  683,  282,  686,  256,  345,  368,  375,  425,  435,  437,  535,  684,  685,  305,  296,  306,  591,  587,
                    588,  144,  145,  146,  147,  148,  149,  150
                    ];

    var iLenA           = sA.length,    iLenB           = sB.length;
    var jA              = 0,            jB              = 0;
    var sIgnoreBuff_A   = [],           sIgnoreBuff_B   = [];


    function iSortIgnoreBuff ()
    {
        var iIgLenA = sIgnoreBuff_A.length, iIgLenB = sIgnoreBuff_B.length;
        var kA      = 0,                    kB      = 0;

        while (kA < iIgLenA  &&  kB < iIgLenB)
        {
            var igA = sIgnoreBuff_A [kA++],  igB = sIgnoreBuff_B [kB++];

            if (aSortOrder[igA]  >  aSortOrder[igB] )   return 1;
            if (aSortOrder[igA]  <  aSortOrder[igB] )   return -1;
        }
        //--- All else equal, longest string loses
        if (iIgLenA > iIgLenB)      return 1;
        if (iIgLenA < iIgLenB)      return -1;

        return 0;
    }


    while (jA < iLenA  &&  jB < iLenB)
    {
        var cA  = sA.charCodeAt (jA++);
        var cB  = sB.charCodeAt (jB++);

        if (cA == cB)
        {
            continue;
        }

        while (bIgnoreForPrimarySort (cA) )
        {
            sIgnoreBuff_A.push (cA);
            if (jA < iLenA)
                cA  = sA.charCodeAt (jA++);
            else
                break;
        }
        while (bIgnoreForPrimarySort (cB) )
        {
            sIgnoreBuff_B.push (cB);
            if (jB < iLenB)
                cB  = sB.charCodeAt (jB++);
            else
                break;
        }

        /*--- Have we reached the end of one or both strings, ending on an ignore char?
            The strings were equal, up to that point.
            If one of the strings is NOT an ignore char, while the other is, it wins.
        */
        if (bIgnoreForPrimarySort (cA) )
        {
            if (! bIgnoreForPrimarySort (cB))   return -1;
        }
        else if (bIgnoreForPrimarySort (cB) )
        {
            return 1;
        }
        else
        {
            if (aSortOrder[cA]  >  aSortOrder[cB] )
                return 1;

            if (aSortOrder[cA]  <  aSortOrder[cB] )
                return -1;

            //--- We are equal, so far, on the main chars.  Where there ignore chars?
            var iBuffSort   = iSortIgnoreBuff ();
            if (iBuffSort)  return iBuffSort;

            //--- Still here?  Reset the ignore arrays.
            sIgnoreBuff_A   = [];
            sIgnoreBuff_B   = [];
        }

    } //-- while (jA < iLenA  &&  jB < iLenB)

    /*--- We have gone through all of at least one string and they are still both
        equal barring ignore chars or unequal lengths.
    */
    var iBuffSort   = iSortIgnoreBuff ();
    if (iBuffSort)  return iBuffSort;

    //--- All else equal, longest string loses
    if (iLenA > iLenB)      return 1;
    if (iLenA < iLenB)      return -1;

    return 0;

} //-- function SortByRoughSQL_Latin1_General_CP1_CS_AS

测试:

var aPhrases    = [
                    'Grails Found',
                    '--Exhibit Visitors',
                    '-Exhibit Visitors',
                    'Exhibit Visitors',
                    'Calls Received',
                    'Ãxhibit Visitors',
                    'Brochures distributed',
                    'exhibit visitors',
                    'bags of Garbage',
                    '^&$Grails Found',
                    '?test'
                ];

aPhrases.sort (SortByRoughSQL_Latin1_General_CP1_CS_AS);

console.log (aPhrases.join ('\n') );

结果:

?test
^&$Grails Found
bags of Garbage
Brochures distributed
Calls Received
exhibit visitors
Exhibit Visitors
-Exhibit Visitors
--Exhibit Visitors
Ãxhibit Visitors
Grails Found

icc97 6 年前

@brockadams'answer 很好,但我在字符串中间有一些边缘大小写,连字符与SQL Server不匹配,我无法很好地找出哪里出错,因此我编写了一个更实用的版本,只过滤掉被忽略的字符,然后比较基于拉丁代码点的数组。

它的性能可能较低,但要理解的代码较少,而且它可以在匹配我在下面添加的SQL测试用例上工作。

我使用的SQL Server数据库带有 latin1_general_100_ci_a s ,因此它不区分大小写,但我将代码保留在这里以区分大小写,很容易切换到不区分大小写的检查,方法是创建包装函数applying tolowercase to the variables.

这两个排序与我的测试用例在排序上没有区别。

/**
*这是SortByroughSQL拉丁语1_General_cp1_cs_as的修改版本
*这有一个更实用的方法,它更基本
*它只做一个字符过滤然后排序
*@链接https://stackoverflow.com/a/3266430/327074
*
*@参数字符串a
*@参数字符串b
*@返回数字-1,0,1
*/
函数latinsqlsort(a,b){
“使用严格”;
//--这是Latin1代码点的主查找表。
//这里通过扩展集\u02af
变量拉丁查找=[
-1151152153154155156157158,2,3,4,5,6159160161162163164,
165166167168169170171172173174175176,0,7,8,9,10,11,12210,
13,14,15,41,16211,17,18,65,69,71,74,76,77,80,81,82,83,19,20,
42、43、44、21、2221422572662843308347352376387419427438459466486,
5295345385576595636641647650661、23、24、25、26、27、28213255265,
28330734635635037435418426436458464485528533536558575594635640,
646648660、29、30、31、32177178179181182183184185186188189,
190191192193194195196197198199200201202203204205207208209,
1,33,53,54,55,56,34,57,35,58215,46,59212,60,36,61,45,72,75,
37、62、63、64、38、70487、47、66、67、68、3921921722123122323350276,
312310316318392390395397295472491489493503495,48511599597601,
60365250957321821622023022223249275311309315317391389394396,
294471490488492502494、49510598596600602651589655229228227226,
23523426826727227127026927427732862852902873243233221314313,
326325320319358357362361356355364363378377380379405404403402,
401400407406393388416421420432431428443944744644434442,
441450449468467474473470469477484483501500499498507506527526,
5405395445435425415605635632567566565564580579578577593592,
61161060960860706066136126176166156164362654653656663662665,
66466766657425822602622612642628127827827730429228928829735337,
3323483483493693713824154094344334484451462479509521524523,
531530552572571569570583582581585635632631634638658657669668673,
6776678,73,79,78680644,50,51,52,40303302301457456455482481,
480225224398497496605604626625620619624623622621334241240,
237236254536365360359430429505550555146775674422300298,
354353、84、85、86、872392382525135122432422452432832730329,
411410413412517516519518547546549548627630629、88、89、90、91、
92、93、94、95、96、97、98、9910010110203104105106107108109110111,
11211311411511611711811911201211221231241251261728129130131,
13213313413513613713813914014114214321432462447248259279228029329291,
3393363383331340341342433673351370372383381384408414386445,
453452454461463460475478465508550852553253250553554555545556557,
53755156833424343344586584618633637639645659649670672679,
6816826832826862563453683754254354337535684684685305296306591587,
588144145146147148149150
;

/**
*对于主排序权重,将忽略一组字符。
*最重要的是连字符和撇号字符。
*一堆控制字符和一些零碎的东西,组成
*其余的。
*
*@参数数字
*@返回布尔值
*@链接https://stackoverflow.com/a/3266430/327074
*/
函数ignoreforimarysort(icharcode){
如果(ICharcode<9){
回归真实;
}

如果(icharcode>=14&icharcode<=31){
回归真实;
}

如果(icharcode>=127&icharcode<=159){
回归真实;
}

如果(icharcode==39 icharcode==45 icharcode==173){
回归真实;
}

返回错误;
}

/ /正规排序
功能比较(A,B){
如果(a==B){
返回0;
}
返回A>B?1∶1;
}

//比较两个数组返回第一个比较差
函数数组(a,b){
返回a.reduce(函数(acc,x,i){
返回acc==0&i<b.长度?比较(x,b[i]):acc;
},0);
}

/**
*将字符串转换为拉丁码位顺序数组
*@参数字符串x
*@返回数组整数数组
*/
函数tolatinor(x){
返回x.split(“”)
//转换为字符代码
.map(函数(x)返回x.charcodeat(0);)
//筛选出忽略的字符
.filter(函数(x)返回!忽略PrimarySort(x);)
//转换为拉丁顺序
.map(函数(x)返回LatinLookup[x];);
}

//转换输入
var chara=托拉廷阶(a)
charb=托拉廷阶(b);

//比较数组
var charscompare=arraycompare(chara,charb);
如果(charscompare!==0){
返回CharsCompare;
}

//回退到筛选后的数组长度
var charslencompare=比较(chara.length,charb.length);
如果(charslencompare!==0){
返回charslencompare;
}

//最后回退到基本长度比较
返回比较(a.长度,b.长度);
}

VAR测试=
“格雷斯发现”
'展览参观者',
'展览参观者',
“展览参观者”,
'已接收的呼叫',
“xhibit访客”,
'分发的手册',
“展览参观者”,
“垃圾袋”,
'^&$grails found',
“?测试,
“612C-520”,
“612 C-122”
‘612C-122’,
“612 C-126 L”,
612C301B',
612C304 B′,
“612C306”,
“612 C306”,
“612 C306 2”
612 C-403 h,
“612C403 O”
612C-403(V)
'612E-306A/B I',
'612E-306A/B O',
‘612C-121’,
“612C-111”,
'-612C-111b′
.sort(拉丁SQL排序).join('<br>');

document.write(tests);


   
    
     我还做了一个18/3195A/2“rel=”nofollow noreferrer“>sql fiddle
    
    to double check it.如果链接断开,这里有一个屏幕截图显示它的外观:
   
   
    
     
    
   
   ng与SQL Server不匹配,我不太清楚哪里出了问题,所以我编写了一个更实用的版本,它过滤掉被忽略的字符,然后根据拉丁代码点比较数组。
   
   
   
    它的性能可能会降低,但要理解的代码更少,而且它可以在与我在下面添加的SQL测试用例相匹配的情况下工作。
   
   
    我使用的是SQL Server数据库
    
     Latin1_General_100_CI_AS
    
    ,所以它不区分大小写,但是我将这里的代码保持为区分大小写,通过创建一个包装函数来应用
    
     toLowerCase
    
    到变量。
   
   
    这两个排序与我的测试用例在排序上没有区别。
   
   
   
   
    
     /**
 * This is a modified version of sortByRoughSQL_Latin1_General_CP1_CS_AS
 * This has a more functional approach, it is more basic
 * It simply does a character filter and then sort
 * @link https://stackoverflow.com/a/3266430/327074
 *
 * @param   {String} a
 * @param   {String} b
 * @returns {Number}   -1,0,1
 */
function latinSqlSort(a, b) {
    'use strict';
    //--- This is the master lookup table for Latin1 code-points.
    //    Here through the extended set \u02AF
    var latinLookup = [
         -1,151,152,153,154,155,156,157,158,  2,  3,  4,  5,  6,159,160,161,162,163,164,
        165,166,167,168,169,170,171,172,173,174,175,176,  0,  7,  8,  9, 10, 11, 12,210,
         13, 14, 15, 41, 16,211, 17, 18, 65, 69, 71, 74, 76, 77, 80, 81, 82, 83, 19, 20,
         42, 43, 44, 21, 22,214,257,266,284,308,347,352,376,387,419,427,438,459,466,486,
        529,534,538,559,576,595,636,641,647,650,661, 23, 24, 25, 26, 27, 28,213,255,265,
        283,307,346,350,374,385,418,426,436,458,464,485,528,533,536,558,575,594,635,640,
        646,648,660, 29, 30, 31, 32,177,178,179,180,181,182,183,184,185,186,187,188,189,
        190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,
          1, 33, 53, 54, 55, 56, 34, 57, 35, 58,215, 46, 59,212, 60, 36, 61, 45, 72, 75,
         37, 62, 63, 64, 38, 70,487, 47, 66, 67, 68, 39,219,217,221,231,223,233,250,276,
        312,310,316,318,392,390,395,397,295,472,491,489,493,503,495, 48,511,599,597,601,
        603,652,590,573,218,216,220,230,222,232,249,275,311,309,315,317,391,389,394,396,
        294,471,490,488,492,502,494, 49,510,598,596,600,602,651,589,655,229,228,227,226,
        235,234,268,267,272,271,270,269,274,273,286,285,290,287,324,323,322,321,314,313,
        326,325,320,319,358,357,362,361,356,355,364,363,378,377,380,379,405,404,403,402,
        401,400,407,406,393,388,417,416,421,420,432,431,428,440,439,447,446,444,443,442,
        441,450,449,468,467,474,473,470,469,477,484,483,501,500,499,498,507,506,527,526,
        540,539,544,543,542,541,561,560,563,562,567,566,565,564,580,579,578,577,593,592,
        611,610,609,608,607,606,613,612,617,616,615,614,643,642,654,653,656,663,662,665,
        664,667,666,574,258,260,262,261,264,263,281,278,277,304,292,289,288,297,335,337,
        332,348,349,369,371,382,415,409,434,433,448,451,462,476,479,509,521,520,524,523,
        531,530,552,572,571,569,570,583,582,581,585,632,631,634,638,658,657,669,668,673,
        677,676,678, 73, 79, 78,680,644, 50, 51, 52, 40,303,302,301,457,456,455,482,481,
        480,225,224,399,398,497,496,605,604,626,625,620,619,624,623,622,621,334,241,240,
        237,236,254,253,366,365,360,359,430,429,505,504,515,514,675,674,422,300,299,298,
        354,353, 84, 85, 86, 87,239,238,252,251,513,512,243,242,245,244,328,327,330,329,
        411,410,413,412,517,516,519,518,547,546,549,548,628,627,630,629, 88, 89, 90, 91,
         92, 93, 94, 95, 96, 97, 98, 99,100,101,102,103,104,105,106,107,108,109,110,111,
        112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,
        132,133,134,135,136,137,138,139,140,141,142,143,246,247,248,259,279,280,293,291,
        339,336,338,331,340,341,342,423,367,373,351,370,372,383,381,384,408,414,386,445,
        453,452,454,461,463,460,475,478,465,508,522,525,532,550,553,554,555,545,556,557,
        537,551,568,333,424,343,344,586,584,618,633,637,639,645,659,649,670,671,672,679,
        681,682,683,282,686,256,345,368,375,425,435,437,535,684,685,305,296,306,591,587,
        588,144,145,146,147,148,149,150
    ];

    /**
     * A bunch of characters get ignored for the primary sort weight.
     * The most important ones are the hyphen and apostrophe characters.
     * A bunch of control characters and a couple of odds and ends, make up
     * the rest.
     *
     * @param   {Number}
     * @returns {Boolean}
     * @link https://stackoverflow.com/a/3266430/327074
     */
    function ignoreForPrimarySort(iCharCode) {
        if (iCharCode < 9) {
            return true;
        }

        if (iCharCode >= 14 && iCharCode <= 31) {
            return true;
        }

        if (iCharCode >= 127 && iCharCode <= 159) {
            return true;
        }

        if (iCharCode == 39 || iCharCode == 45 || iCharCode == 173) {
            return true;
        }

        return false;
    }

    // normal sort
    function compare(a, b) {
        if (a === b) {
            return 0;
        }
        return a > b ? 1 : -1;
    }

    // compare two arrays return first compare difference
    function arrayCompare(a, b) {
        return a.reduce(function (acc, x, i) {
            return acc === 0 && i < b.length ? compare(x, b[i]) : acc;
        }, 0);
    }

    /**
     * convert a string to array of latin code point ordering
     * @param   {String} x
     * @returns {Array}    integer array
     */
    function toLatinOrder(x) {
        return x.split('')
            // convert to char codes
            .map(function(x){return x.charCodeAt(0);})
            // filter out ignored characters
            .filter(function(x){return !ignoreForPrimarySort(x);})
            // convert to latin order
            .map(function(x){return latinLookup[x];});
    }

    // convert inputs
    var charA = toLatinOrder(a),
        charB = toLatinOrder(b);

    // compare the arrays
    var charsCompare = arrayCompare(charA, charB);
    if (charsCompare !== 0) {
        return charsCompare;
    }

    // fallback to the filtered array length
    var charsLenCompare = compare(charA.length, charB.length);
    if (charsLenCompare !== 0) {
        return charsLenCompare;
    }

    // Final fallback to a basic length comparison
    return compare(a.length, b.length);
}

var tests = [
    'Grails Found',
    '--Exhibit Visitors',
    '-Exhibit Visitors',
    'Exhibit Visitors',
    'Calls Received',
    'Ãxhibit Visitors',
    'Brochures distributed',
    'exhibit visitors',
    'bags of Garbage',
    '^&$Grails Found',
    '?test',
    '612C-520',
    '612-C-122',
    '612C-122 I',
    '612-C-126 L',
    '612C-301 B',
    '612C-304 B',
    '612C-306',
    '612-C-306',
    '612-C-306 2',
    '612-C-403 H',
    '612C403 O',
    '612-C-403(V)',
    '612E-306A/B I',
    '612E-306A/B O',
    '612C-121 O',
    '612C-111 B',
    '- -612C-111 B'
].sort(latinSqlSort).join('<br>');

document.write(tests);
    
   
   
    我也做了一个
    
     SQL fiddle
    
    再检查一下。如果链接断开,以下是它的外观截图:

bobince 14 年前

抱歉,javascript没有排序规则功能。您得到的唯一字符串比较是直接使用 String ,由返回 charCodeAt() .

对于基本多语言平面内的字符,这与二进制排序规则相同,因此,如果需要JS和SQL Server同意(不管怎样,忽略星状平面),我认为这是唯一的方法。(总之,除了在JS中构建一个字符串排序器和仔细复制SQL Server的排序规则之外。那里没有什么乐趣。)

(什么是用例,为什么需要匹配?)