CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008-r2sql-order-by
itsMacyAnn
itsMacyAnn

Reputation: 71

SQL Sort of mixed numbers and alphanumeric Value

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

Upvotes: 1

Views: 385

Answers (1)

gofr1
gofr1

Reputation: 15977

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.

Upvotes: 1

Related Questions