Geek Answers Handbook

Alphanumeric Sorting in Sql Server 2008

Can someone help me how to sort this?
I have a dynamic list of templates that I want to sort, it contains alphanumeric values ​​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 is as follows:

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

But all I want to show is similar to this result:

 AA A1 A2 A8 A11 A12 A20 AB BA B2 B3 B6 B10 B21 B100 BB
+5
source share
5 answers
 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))

It will display:

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

For your sample data, this is close:

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

But you want all alpha to be last, so case is required:

 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
+1
source 
 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 network and stack overflow before publishing.

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

+1
source

I would split alpha and several 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
0
source

Use cross apply to simplify calculations in a row

 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)
0
source

More articles:


All Articles