CODEWITHSUNDEEP
sqloracle-database
Techie
Techie

Reputation: 1651

sorting in sql based on a part of a column(number)

I have a column with values as below

18 ABC
45 XYZ
1 ABC
83 DEF
22 XYZ
4 ABC

I want them to be sorted as below while pulling the values from oracle DB

1 ABC
4 ABC
18 ABC
22 XYZ
45 XYZ
83 DEF

when I use order by substr(column,1) it is not giving the result as expected 4 ABC will go down after 22 XYZ

Upvotes: 1

Views: 74

Answers (2)

user2422457
user2422457

Reputation:

You may do the ff:

order by to_number(substr(column, 1, instr(column, ' ')))

Upvotes: 2

Gerardo Lima
Gerardo Lima

Reputation: 6703

Did you try something like ORDER BY CAST(substr(column, 2) AS INT)?

Upvotes: 0

Related Questions