Order VARCHAR AND NUMBER

Question

Column : VARCHAR

I have this select :

SELECT column FROM TABLE

ae
10000
10005
ad
ab
ac
10010

and i want to order them like this : (Number ordered then Varchar ordered)

SELECT column FROM TABLE ORDER BY column

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

Never a mix of letters and digits? Do you want 2 before or after 10? — jarlh, Nov 10 '16 at 12:54
Have you tried you select statement? And what have you got as a result? — atokpas, Nov 10 '16 at 12:57
select * from table order by column desc give me varhars then numbers — zebiri djallil, Nov 10 '16 at 12:59
Whats the output of `select * from v$nls_parameters where parameter='NLS_SORT';`? If you set this to binary then it works as you have expected. — atokpas, Nov 10 '16 at 13:00
So - where should 12abs be ordered? Also, should 200 come before or after 12110? — , Nov 10 '16 at 13:00

score 2 · Answer 1 · answered Nov 10 '16 at 13:03

Because the column is a character column the numbers are going to be ordered by a binary sort. You want to order them numerically, which means you need 2 ORDER BY clauses

A numeric, where only numeric characters are considered
A standard binary sort

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

The regular expression is

^ - anchor to the beginning of the string
\d - match only numbers
+ - match the previous expression any number of times
$ - anchor to the end of the string

It serves to enforce that only numbers exist prior to converting the column to a number for the initial sort.

More generally, it's never wise to put numbers and characters in the same column, for the reason that you've discovered and because it prevents you from enforcing that the data type is correct.

Lastly, consider whether you actually need to order your query at all, sorting is usually only necessary for display purposes or for applying some business logic to the "top" N elements of a sorted data set.

If the first `column_name` evaluated in the `CASE` expression is not a number then the `NULL` generated by `CASE` will be a "string" `NULL`. If later there is a "number", `CASE` will produce a number and the runtime will throw an error. You need to add `else cast (null as number)` to the CASE expression to prevent that. — , Nov 10 '16 at 13:08
Can you demonstrate that @mathguy? I've tested this and even replaced the `table` with the sub-expression `(select * from table_name order by column_name desc)` in order to force the case that you're talking about. There's a small danger of predicate pushing, yes, but adding a cast won't necessarily help with that. — Ben, Nov 10 '16 at 13:14
Hmm... I take it back. I just tested it and it doesn't seem to behave the way I thought. I had run into that problem in the past, perhaps with `UNION`s so I assumed that would be a problem in all similar situations. In any case, your solution works as-is. — , Nov 10 '16 at 13:37

Order VARCHAR AND NUMBER

1 Answers1