Preciso de ajuda rápida na classificação de dados no lado do SQL. estou usandoSqlserver 2012 (bom se a resposta der com o novo recurso).

Eu já pesquiso alguns links comoClassificação alfanumérica , Classificação alfanumérica em Sqlserver - projeto de código. Mas não dá o resultado desejado.

Ainda o que eu tentei:

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

Agora o que eu quero é:se a mesma sequência de caracteres encontrar na parte do alfabeto, classifique a primeira e depois a numérica (considere também o espaço, se possível, ou você pode fornecer o resultado sem espaço, como o acampamento no.41 e o acampamento no 41, na mesma ordem)

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