CODEWITHSUNDEEP
oracle-database
imsome1
imsome1

Reputation: 1192

How to order by field mixed with string and number in Oracle?

These are the field (crane_no) values to be sorted
QC11
QC10
QC9

I tried the following query:

select * from table order by crane_no DESC

but query results does not give in an order because the field is mixed with staring and number (Example:QC12). I get following results for above query:
QC9
QC11
QC10

I want the results to be in order (QC9, QC10, QC11). Thanks

Upvotes: 4

Views: 6761

Answers (3)

gregory
gregory

Reputation: 12895

If the data isn't huge, I'd use a regex order by clause: 

select 
  cran_no 
from your_table
order by 
  regexp_substr(cran_no, '^\D*') nulls first,
  to_number(regexp_substr(cran_no, '\d+'))

This looks for the numbers in the string, so rows like 'QCC20', 'DCDS90' are ordered properly; it also takes care of nulls.

Upvotes: 5

BobC
BobC

Reputation: 4416

select ...
order by to_number( substr( crane_no,3 )) desc

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

One approach is to extract the numeric portion of the crane_no columns using SUBSTR(), cast to an integer, and order descending by this value.

SELECT *
FROM yourTable
ORDER BY CAST(SUBSTR(crane_no, 3) AS INT) DESC

Note that I assume in my answer that every entry in crane_no is prefixed with the fixed width QC. If not, then we would have to do more work to identify the numerical component.

Upvotes: 1

Related Questions