sql server - Alphanumeric Sort

Question

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

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

Still What I have try :

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

Now what I want is : if same string find in alphabet part sort on that first and then numeric (consider space also if possible or you may give the result without space like Campsite no.41 and Campsite No. 41 will give in same order)

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

Answer 1

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

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

Basically, You had 4 major problems:

• Your alphabetical sort expression assumed that every row have numbers in it.
• Your alphabetical sort expression contained the numbers as well as the text.
• Your numeric sort expression had both numeric and alphabetical values.
• Because of article 3, you couldn't cast your numeric sort expression to a numeric type, and this is why you would get you a string sort.

See this sql fiddle