How to sort string with numbers numerically in SQL?

Question

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

What's the specific database? The answer depends on the available database functions. — The Impaler
– The Impaler, Commented Sep 19, 2018 at 0:31

sticky bit · Accepted Answer · 2018-09-19 00:40:44Z

2

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);

answered Sep 19, 2018 at 0:40

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joel Coehoorn · Accepted Answer · 2018-09-19 00:36:12Z

1

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

answered Sep 19, 2018 at 0:36

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Collectives™ on Stack Overflow

How to sort string with numbers numerically in SQL?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related