CODEWITHSUNDEEP
sqloracle-databasesql-order-by
David Faizulaev
David Faizulaev

Reputation: 5721

SQL Order by string, numbers appear last

I have a query that return a string column which I would like to sort by. When I sort by ASC/DESC, I'm getting the same result. Here is an output example:

AVI
ksky site
Secure East
Shlomi
VSEStaging
1200165
120186
120186
120186
120032

But I would to have the numbers before the alphabetic string. For example for ASC:

1200165
120186
120186
120186
120032
AVI
ksky site
Secure East
Shlomi
VSEStaging

For example for DESC:

VSEStaging
Shlomi
Secure East
ksky site
AVI
120032

I currently use REGEX like so:

REGEXP_SUBSTR(UPPER(COLUMN_NAME), '^\D*') ,
TO_NUMBER(REGEXP_SUBSTR(UPPER(COLUMN_NAME), '\d+'))

Please assist.

Upvotes: 0

Views: 1229

Answers (2)

user6585756
user6585756

Reputation: 1

You could try something different like this:

order by translate(upper(col), '1234567890', 'abcdefghij')

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269543

You can use regexp_like(). For the ascending sort:

order by (case when regexp_like(col, '^[0-9]+$') then 1 else 2 end) asc,
         col asc

Upvotes: 3

Related Questions