CODEWITHSUNDEEP
sqlsql-serversql-order-by
itsMacyAnn
itsMacyAnn

Reputation: 71

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

Upvotes: 5

Views: 320

Answers (5)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

For your sample data, this comes close:

order by left(pattern, patindex('%[0-9]%', pattern)),
         patindex('%[0-9]%', pattern),
         len(pattern) asc,
         pattern

But, you want the all alphas to be last, so a case is needed (I think):

order by left(pattern, patindex('%[0-9]%', pattern)),
         (case when pattern like '%[0-9]%'
               then patindex('%[0-9]%', pattern)
               else 999
          end),
         len(pattern) asc,
         pattern

Upvotes: 1

gofr1
gofr1

Reputation: 15997

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

Upvotes: 1

Serg
Serg

Reputation: 22811

Use cross apply to simplify in-row calculations

select pattern
from pattern
cross apply (
    select leftLen = isnull(nullif(patindex('%[0-9]%', pattern),0) - 1, len(pattern))
          ,totalLen = len(pattern)  
    ) c
order by 
 case leftLen when totalLen then 2 else 1 end,
 left(Pattern, leftLen),
 cast(right(Pattern, totalLen-leftLen) as int)

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522817

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/

Upvotes: 1

Paweł Dyl
Paweł Dyl

Reputation: 9143

I would separater alpha and num parts:

ORDER BY
    CASE WHEN PATINDEX('%[0-9]%', Pattern)=0 THEN 1 ELSE 0 END,--Put no-nums last
    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 SUBSTRING(Pattern, PATINDEX('%[0-9]%', Pattern), LEN(Pattern)) END

Upvotes: 0

Related Questions