字母数字排序 [英] Alphanumeric Sort

问题描述

我需要在SQL端对数据进行排序的快速帮助.我正在使用Sqlserver 2012(如果答案带有新功能，则很好).

I need quick help in sorting data at SQL side. I am using Sqlserver 2012 (good if answer give with the new feature).

我已经搜索了一些链接，例如按字母数字排序，在Sqlserver中对字母数字字符串进行排序-代码项目.但是并不能达到预期的效果.

I already search some links as Sorting in alphanumeric , Alphanumeric string Sorting in Sqlserver - Code project. But does not give the desired result.

仍然尝试一下:

CREATE TABLE dbo.Section
    (
           Section varchar(50) NULL
    )
    INSERT INTO dbo.Section (Section.Section) VALUES ('Campsit no.43')
    INSERT INTO dbo.Section (Section.Section) VALUES ('Campsit no.41')
    INSERT INTO dbo.Section (Section.Section) VALUES ('Campsite No. 11')
    INSERT INTO dbo.Section (Section.Section) VALUES ('Campsite No. 1')
    INSERT INTO dbo.Section (Section.Section) VALUES ('Campsite No. 12')
    INSERT INTO dbo.Section (Section.Section) VALUES ('Campsite No. 2')
    INSERT INTO dbo.Section (Section.Section) VALUES ('Campsite No. 3')
    INSERT INTO dbo.Section (Section.Section) VALUES ('Campsite No. 4')
    INSERT INTO dbo.Section (Section.Section) VALUES ('Campsite No. 40')
    INSERT INTO dbo.Section (Section.Section) VALUES ('Campsite No. 41')
    INSERT INTO dbo.Section (Section.Section) VALUES ('Campsite no.20')
    INSERT INTO dbo.Section (Section.Section) VALUES ('Campsite no.41')
    INSERT INTO dbo.Section (Section.Section) VALUES ('Cabin')
    INSERT INTO dbo.Section (Section.Section) VALUES ('Group Tent Campsite')
    INSERT INTO dbo.Section (Section.Section) VALUES ('Tent Campsite')
    INSERT INTO dbo.Section (Section.Section) VALUES ('test1')
    INSERT INTO dbo.Section (Section.Section) VALUES ('test2')
    INSERT INTO dbo.Section (Section.Section) VALUES ('test11')
    SELECT Section
    FROM dbo.Section
    --Show normal Sort
    SELECT Section
    FROM dbo.Section
    ORDER BY Section
    --Show AlphaNumberic Sort
    SELECT Section
    FROM dbo.Section
    ORDER BY LEFT(Section,PATINDEX('%[0-9]%',Section)), -- alphabetical sort
             CONVERT(varchar(50),SUBSTRING(Section,PATINDEX('%[0-9]%',Section),LEN(Section))) -- numerical sort
    --cleanup our work
    --DROP Table dbo.Section

现在我要的是:如果首先在字母部分上找到相同的字符串，然后在数字上进行排序(如果可能，还请考虑空格，否则您可以给出没有空格的结果，例如Campsite No.41和Campsite No.41)会以相同的顺序给出)

Actual Result          Expected Result
Campsit no.41          Campsit no.41
Campsit no.43          Campsit no.43
Campsite No. 1         Campsite No. 1
Campsite No. 11        Campsite No. 2
Campsite No. 12        Campsite No. 3
Campsite No. 2         Campsite No. 4
Campsite No. 21        Campsite No. 11
Campsite No. 3         Campsite No. 12
Campsite No. 4         Campsite No. 21
Campsite No. 40        Campsite No. 40
Campsite No. 41        Campsite No. 41
Campsite no.20         Campsite no.20 --this will good to come here, if possible or if not, then remove space and set approriate
Campsite no.41         Campsite no.41 --this will good to come here, if possible or if not, then remove space and set approriate
Group Tent Campsite    Group Tent Campsite
Tent Campsite          Tent Campsite
test1                  test1
test11                 test2
test2                  test11

推荐答案

这里有个提示:每当您在排序方面遇到问题时，都可以将按项排序添加到select子句中.这将使您能够查看排序依据是否实际上是您要排序的依据:

Here's a tip: Whenever you are having problems with the sort, add order by items to your select clause. this will enable you to see if what you are sorting by is actually what you want to sort by:

SELECT Section,
        CASE WHEN PATINDEX('%[0-9]%',Section) > 1 THEN
          LEFT(Section,PATINDEX('%[0-9]%',Section)-1)
        ELSE 
          Section
        END As alphabetical_sort, -- alphabetical sort
        CASE WHEN PATINDEX('%[0-9]%',Section) > 1 THEN
          CAST(SUBSTRING(Section,PATINDEX('%[0-9]%',Section),LEN(Section)) as float)
        ELSE
          NULL
        END As Numeric_Sort
FROM dbo.Section
ORDER BY alphabetical_sort, Numeric_Sort

正确排序后，我要做的就是将case语句移到order by子句:

After I've got the sort correctly, All I had to do is move the case statements to the order by clause:

SELECT Section
FROM dbo.Section
ORDER BY 
    CASE WHEN PATINDEX('%[0-9]%',Section) > 1 THEN
        LEFT(Section,PATINDEX('%[0-9]%',Section)-1)
    ELSE 
        Section
    END , -- Alphabetical sort
    CASE WHEN PATINDEX('%[0-9]%',Section) > 1 THEN
        CAST(SUBSTRING(Section,PATINDEX('%[0-9]%',Section),LEN(Section)) as float)
    ELSE
        NULL
    END  -- Numeric sort

基本上，您有4个主要问题:

Basically, You had 4 major problems:

• 您的字母排序表达式假定每一行中都有数字.
• 您的字母排序表达式包含数字和文本.
• 您的数字排序表达式同时具有数字和字母值.
• 由于第3条，您无法将数字排序表达式转换为数字类型，这就是为什么要对字符串进行排序的原因.

查看此sql小提琴

这篇关于字母数字排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！