SQL Sort of mixed numbers and alphanumeric Value

Question

I'm going to sort that column contains the value of numbers and alphanumeric value.

But I got the wrong sort (order by) can anyone help me regarding with this problem?

I'm using SQL SERVER 2008 R2, here's the sample:

DECLARE @TableTester TABLE (TESTER VARCHAR(50));

INSERT @TableTester VALUES
('1'),
('2'),
('3'),
('4'),
('10'),
('20'),
('5 ; 6'),
('122'),
('256 ; 78'),
('U1 ; U2'),
('U3 ; 7'),
('C1'),
('U4'),
('WC23'),
('WC214 ; U4'),
('U10');

SELECT Tester FROM @TableTester
ORDER BY CASE WHEN PATINDEX('%[0-9]%', Tester) > 0 THEN 0 ELSE 1 END, 
CASE WHEN PATINDEX('%[0-9]%', Tester) > 0 
THEN LEFT(Tester, PATINDEX('%[0-9]%', Tester)-1) ELSE Tester END

OUTPUT:

1
2
3
4
10
20
5 ; 6
122
256 ; 78
C1
U4
U10
U1 ; U2
U3 ; 7
WC23
WC214 ; U4

DESIRED RESULT:

1
2
3
4
5 ; 6
10
20
122
256 ; 78
C1
U1 ; U2
U3 ; 7
U4
U10
WC23
WC214 ; U4

Possible duplicate of [SQL Server 2008 - order by strings with number numerically](http://stackoverflow.com/questions/20240313/sql-server-2008-order-by-strings-with-number-numerically) — hatchet - done with SOverflow, Sep 17 '16 at 04:08
Also, if the answers to the duplicate question are hard to follow, there is this: http://www.essentialsql.com/use-sql-server-to-sort-alphanumeric-values/ — hatchet - done with SOverflow, Sep 17 '16 at 04:12
@hatchet , but I have multiple values in my column that separated semicolon ";" — itsMacyAnn, Sep 17 '16 at 04:18
You seem to be saying that question is not a duplicate because it doesn't handle the semicolon issue present in your case. So I'll retract my close vote. Unfortunately, I don't have SQL Server handy right now, so can't try out a solution. — hatchet - done with SOverflow, Sep 17 '16 at 04:26

gofr1 · Accepted Answer · 2016-09-17T08:01:07.603

Once again, normalize your data - this ordering is weird:

SELECT TESTER 
FROM @TableTester
ORDER BY 
    CASE WHEN TESTER LIKE '[A-Z]%' THEN LEFT(TESTER,PATINDEX('%[0-9]%',TESTER)-1) ELSE NULL END,
    CAST(
        CASE WHEN TESTER LIKE '%[0-9]%' 
                THEN REPLACE(SUBSTRING(TESTER,PATINDEX('%[0-9]%',TESTER),CHARINDEX(' ;',TESTER+' ;')-1),' ;','') 
                ELSE TESTER END
                        as int)

The main idea is to get letters and digits separated and than use cast to make them integer and sort first by letters than by integer.

SQL Sort of mixed numbers and alphanumeric Value

1 Answers1