CODEWITHSUNDEEP
db2db2-400winsql
Lily
Lily

Reputation: 9

WinSQL ORDER BY alphanumeric DB2 syntax

I have the following set of data:

33
5A
5B
12
34A
2
34B
2B
11
10
12A

When I run the following SQL in WinSQL:

with input (f) as ( 
values ('33'), ('5A'), ('5B'),('12'), ('34A'),('2'), ('34B'), ('2B'), ('11'), ('10'), ('12A')
  )
SELECT f 
FROM input
ORDER BY f

I get the following result:

10
11
12
12A
2
2B
33
34A
34B
5A
5B

I would however want the result to be in this order:

2
2B
5A
5B
10
11
12
12A
33
34A
34B

I have tried the following:

with input (f) as ( 
    values ('33'), ('5A'), ('5B'),('12'), ('34A'),('2'), ('34B'), ('2B'), ('11'), ('10'), ('12A')
  )
SELECT 
f
FROM input
ORDER BY CAST(CASE
                 WHEN f LIKE '[0-9]' THEN LEFT(CONCAT(0,f),1)
                 WHEN f LIKE '[0-9]%' THEN LEFT(CONCAT(0,f),1)
                 WHEN f LIKE '[0-9][0-9]%' THEN LEFT(f ,2)
                 ELSE NULL 
              END AS INT), f

But it doesn't work.

NOTE: This is for DB2 and all SQL-server functions (such as PATINDEX, SIGNED/UNSIGNED) are not available.

Upvotes: 0

Views: 280

Answers (1)

MandyShaw
MandyShaw

Reputation: 1156

Try this:

ORDER BY CAST(replace(replace(replace(f,'A',''),'B',''),' ','') AS INT), f

(i.e. construct a sort field by getting rid of the non-numeric elements & converting to numeric, and then tie-break using the original value).

Upvotes: 1

Related Questions