sql - Order VARCHAR AND NUMBER -

this question has answer here:

• how sort numbers first oracle sql query? 2 answers

column : varchar 

i have select :

select column table

ae 10000 10005 ad ab ac 10010 

and want order them : (number ordered varchar ordered)

select column table order column

1 - 10000 2 - 10005 3 - 10010 4 - ab 5 - ac 6 - ad 7 - ae 

because column character column numbers going ordered binary sort. want order them numerically, means need 2 order clauses

1. a numeric, numeric characters considered
2. a standard binary sort

select column_name   table  order case when regexp_like(column_name, '^\d+$') to_number(column_name) end         , column_name 

the regular expression

• ^ - anchor beginning of string
• \d - match numbers
• + - match previous expression number of times
• $ - anchor end of string

it serves enforce numbers exist prior converting column number initial sort.

more generally, it's never wise put numbers , characters in same column, reason you've discovered , because prevents enforcing data type correct.

lastly, consider whether need order query @ all, sorting necessary display purposes or applying business logic "top" n elements of sorted data set.