SQL Oracle Sort string (numbers) and (letters with numbers)

Question

I am new to oracle and I have a problem. I have a column named file_id.

When I do an order by it sorts strings such as

edit: I would like it to sort this way.

The answer below works perfectly. Only other problem I ran into now..I have records that are blank. How could I make the blank records order at the end?

1 
1 
10 
11 
11 
12 
31 
100 
300 
4200 
BLANK 
BLANK 
BLANK 
BLANK 
BLANK 
B14 
B18 
B170

Thank you for your help.

What does "properly" mean; you'd like a numeric sort? followed by a binary sort? — Ben
– Ben, Commented Mar 22, 2013 at 14:25
I have edited to show how I would like to do the sort. Thank you for such a quick response. — user2199531
– user2199531, Commented Mar 22, 2013 at 14:30
@user2199531 add an case to the start of the order by .. order by case when col is null then 2 else 1 end, regexp_substr(... — DazzaL
– DazzaL, Commented Mar 22, 2013 at 15:49
@user2199531, add "NULLS LAST" to the order by to sort the nulls last. — Ronnis
– Ronnis, Commented Mar 22, 2013 at 21:51

Egor Skriptunoff · Accepted Answer · 2013-03-22 14:36:53Z

42

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

fiddle

edited Mar 22, 2013 at 14:36

answered Mar 22, 2013 at 14:31

Egor Skriptunoff

23.8k2 gold badges38 silver badges68 bronze badges

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

7 Comments

Gary Stanton Over a year ago

Love this. I'm combining it with a replace to remove punctuation and normalise casing for a generic natural sort.

regexp_substr(REGEXP_REPLACE(TRIM(LOWER(coulmn)), '[[:punct:]]', ''), '^\D*') nulls first, to_number(regexp_substr(REGEXP_REPLACE(TRIM(LOWER(column)), '[[:punct:]]', ''), '\d+'))

aashii Over a year ago

if I have decimal digits. it does not give correct result. I have 1.3, 1, 1.9. It should sort as 1, 1.3, 1.9, but actual result is 1.3, 1.9, 1

Egor Skriptunoff Over a year ago

@aashii - Replace \d+ with [0-9.,]+. You might also need to replace dots with commas (or reverse) before to_numbering them

wonsuc Over a year ago

regexp_substr(column, '^\D*') nulls first what does this code means?

Egor Skriptunoff Over a year ago

@anishjp - Add nulls first at the end, see fiddle

|

Northmoor · Accepted Answer · 2015-08-21 10:19:55Z

This is an old question, but it was the first hit on google so I thought I'd share an alternative solution:

select column
from table
order by 
  LPAD(column, 10)

The LPAD function pads the left-side of the string with spaces so that the results will be sorted numerically. This works for non-numeric values, and null values will be sorted last. This works well if you know the maximum length of the strings to be sorted (you may need to adjust the second parameter to suit your needs).

Source: http://www.techonthenet.com/oracle/questions/sort1.php

EDIT:
I noticed that while my solution works well for my case, the output is slightly different from the accepted answer (http://www.sqlfiddle.com/#!4/d935b8/2/0):

1
1
10
11
11
12
31
100
110
114
300
A14
A18
4200
A170
(null)
(null)

4200 should come after 300. For my situation this is good enough, but this may not always be the case.

Francisco M · Accepted Answer · 2017-10-18 17:28:49Z

3

Based on the previous solution:

SELECT column
FROM table
ORDER BY LPAD(column, (SELECT MAX(LENGTH(column)) FROM table)) ASC

The advantage of this approach is that you don't need know the table column size.

edited Oct 18, 2017 at 17:28

answered Oct 18, 2017 at 15:59

Francisco M

3034 silver badges11 bronze badges

Collectives™ on Stack Overflow

SQL Oracle Sort string (numbers) and (letters with numbers)

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related