ayakamacy ayakamacy - 2 months ago 10
SQL Question

Alphanumeric Sorting in Sql Server 2008

can anyone help me how to sort this?

I have a dynamical list of pattern that I want to sort, it contains alphanumeric value and letters.

CREATE TABLE dbo.Pattern (Pattern varchar(50) NULL)
INSERT INTO dbo.Pattern (Pattern) VALUES ('A11')
INSERT INTO dbo.Pattern (Pattern) VALUES ('A12')
INSERT INTO dbo.Pattern (Pattern) VALUES ('A8')
INSERT INTO dbo.Pattern (Pattern) VALUES ('A2')
INSERT INTO dbo.Pattern (Pattern) VALUES ('B6')
INSERT INTO dbo.Pattern (Pattern) VALUES ('B21')
INSERT INTO dbo.Pattern (Pattern) VALUES ('B10')
INSERT INTO dbo.Pattern (Pattern) VALUES ('B3')
INSERT INTO dbo.Pattern (Pattern) VALUES ('B100')
INSERT INTO dbo.Pattern (Pattern) VALUES ('B2')
INSERT INTO dbo.Pattern (Pattern) VALUES ('AA')
INSERT INTO dbo.Pattern (Pattern) VALUES ('BA')
INSERT INTO dbo.Pattern (Pattern) VALUES ('A20')
INSERT INTO dbo.Pattern (Pattern) VALUES ('AB')
INSERT INTO dbo.Pattern (Pattern) VALUES ('BB')

SELECT Pattern FROM dbo.Pattern ORDER BY Pattern

DROP Table dbo.Pattern


The result shows like this:

A11
A12
A2
A20
A8
AA
AB
B10
B100
B2
B21
B3
B6
BA
BB


But All I want to show is like this result:

AA
A1
A2
A8
A11
A12
A20
AB
BA
B2
B3
B6
B10
B21
B100
BB

Answer
SELECT Pattern
FROM dbo.Pattern 
ORDER BY LEFT(Pattern,1), 
        CASE WHEN SUBSTRING(Pattern,2,LEN(Pattern)) LIKE '%[0-9]%' THEN CAST(SUBSTRING(Pattern,2,LEN(Pattern)) as int) 
            WHEN SUBSTRING(Pattern,2,LEN(Pattern)) = 'A' THEN 0
            ELSE 10000000 END,
            SUBSTRING(Pattern,2,LEN(Pattern))

Will output:

Pattern
AA
A2
A8
A11
A12
A20
AB
BA
B2
B3
B6
B10
B21
B100
BB