Alphanumeric Sorting in Sql Server 2008

Question

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 1

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

Answer 2

SELECT Pattern
FROM dbo.Pattern
ORDER BY CASE WHEN PATINDEX('%[0-9]%', Pattern) > 0
              THEN LEFT(Pattern, PATINDEX('%[0-9]%', Pattern)-1)
              ELSE Pattern
         END,
         CASE WHEN PATINDEX('%[0-9]%', Pattern) > 0
              THEN CONVERT(INT, SUBSTRING(Pattern, PATINDEX('%[0-9]%', Pattern), LEN(Pattern)))
              ELSE 0
         END

Please search the net and Stack Overflow before posting.

Reference: http://www.essentialsql.com/use-sql-server-to-sort-alphanumeric-values/