CODEWITHSUNDEEP
sql
Jimmy
Jimmy

Reputation: 3

How to sort string with numbers numerically in SQL?

this is how it is currently sorted:

CRI-SU1
CRI-SU10
CRI-SU11
CRI-SU2
CRI-SU3
CRI-SU4
CRI-SU5
CRI-SU6
CRI-SU7
CRI-SU8
CRI-SU9

I wanted it to be sorted numerically like:

CRI-SU1
CRI-SU2
CRI-SU3
CRI-SU4
CRI-SU5
CRI-SU6
CRI-SU7
CRI-SU8
CRI-SU9
CRI-SU10
CRI-SU11

How do you sort it in SQL? this is my sql statement...

SELECT systemUnitID FROM systemUnit ORDER BY systemUnitID

Upvotes: 0

Views: 492

Answers (2)

sticky bit
sticky bit

Reputation: 37472

If the prefix always reads "CRI-SU", you can try to replace it with the empty string by using replace(). Then cast the result to an integer with cast() and order by the casted value. This should work in many DBMS.

SELECT systemunitid
       FROM systemunit
       ORDER BY cast(replace(systemunitid, 'CRI-SU', '') AS integer);

Upvotes: 2

Joel Coehoorn
Joel Coehoorn

Reputation: 415630

SELECT systemUnitID 
FROM systemUnit
ORDER BY CAST(REPLACE(systemUnitID, 'CRI-SU', '') As int)

Upvotes: 1

Related Questions