CODEWITHSUNDEEP
oracle-databasesql-order-by
Selva
Selva

Reputation: 1670

how to use order by in alphanumeric column in oracle

In my table one of column i have a value like below

Y-1
Y-2
Y-3
Y-4
Y-5
Y-6
Y-7
Y-8
Y-9
Y-10
Y-11
Y-12
Y-13
Y-14

when i am order by this column its working fine if the row has value up to Y-9 other wise my result is wrong like below.

Y-1
Y-10
Y-11
Y-12
Y-13
Y-14
Y-2
Y-3
Y-4
Y-5
Y-6
Y-7
Y-8
Y-9

But i want the output like below

Y-1
Y-2
Y-3
Y-4
Y-5
Y-6
Y-7
Y-8
Y-9
Y-10
Y-11
Y-12
Y-13
Y-14

How to acheive the above result.i am using oracle database.Any help will be greatly appreciated!!!!!

Upvotes: 4

Views: 1223

Answers (3)

Michael Piankov
Michael Piankov

Reputation: 1997

I think the good way is to get constant length field

select col from t
order by substr(col, 1, 2)|| lpad(substr(col, 3),5,'0')

it will correct work only with two nondigit simbol in begining of string up to 99999 number

Upvotes: 0

user5683823
user5683823

Reputation:

Assuming the data is in a table t with a column col and the structure is an alphabetical string followed by dash followed by a number, and both the alphabetical and the number are always not NULL, then:

select col from t
order by substr(col, 1, instr(col, '-')), to_number(substr(col, instr(col, '-')+1))

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133360

You can use an order by manipulatinng the column content and cast to number eg:

 order by substr(col1, 1,2), TO_NUMBER(sustr(col1, 3,10))

Upvotes: 1

Related Questions