[SOLVED] How can I sort a table by a column for numeric letter first, then case sensitive for alphabet letters

How can I sort a table by a column for numeric letter first, then case sensitive for alphabet letters

How can I sort a table by a column varchar2 with characters in varying cases: upper, lower, numeric string

For example, when I do an order by the column NAME, the data of the column are:

ANNIE
BOB
Daniel
annie
bob
1abc

The expected result is:

1abc    
ANNIE
annie
BOB
bob
Daniel

Solution

This is complicated. Normal sort order is aa, aA, Aa, AA, ab, aB, Ab, AB, a1, A1, 1a, 1A. So same names are grouped together and then lower case comes first. Digits come after Z.

This is close to what you are after. You want Ben to come before BOB, because you care about BEN being before BOB in the first place and only then about O being capital and e being not. However, you want digits come before a and upper case coming before lower case. That makes a great difference at last.

You cannot do this easily, because while you want words (bob, BOB) be grouped as in default ordering, you want single characters be treated differently. You can first order by lower or upper to get the grouping, but that will put numbers last, you can then use binary order to get A before a.

order by lower(name), nlssort(name, 'NLS_SORT = BINARY');

I think this is as close as you get with built-in stuff. Digits last. If you want to stick to your special order, you will have to write a function for it and use that.

order by my_own_sort_order(name);

EDIT (after acceptance :-) On second thought: you want the original sort behavior only with toggled upper/lower case consideration. You can use TRANSLATE for this:

order by translate(name,
   'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz', 
   'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ');