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

Question

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

Wouldn't that result be expected as substr('22 XYZ',1) should give 2 and thus be sorted before 4? — jpw
– jpw, Commented Jun 26, 2013 at 12:36

user2422457 · Accepted Answer · 2013-06-26 12:37:00Z

2

You may do the ff:

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

answered Jun 26, 2013 at 12:37

user2422457

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

2 Comments

it worked, thanks but what if there is no space between the number and the word like 4ABC ?

I can give a number instead of instr if I know how many digits it would be, but this may change over the time..hmmm

Gerardo Lima · Accepted Answer · 2013-06-26 12:36:17Z

0

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

answered Jun 26, 2013 at 12:36

Gerardo Lima

6,7613 gold badges34 silver badges51 bronze badges