Oracle sort varchar2 column with special characters last

How can I sort in Oracle a Varchar2 or NVarchar2 column to be in my own custom defined order. Or are any existing options available that will put letters first, then numbers, then all special characters.

Our first approach was using a function that does some manually mapping of characters to numbers.

select id, sorted_column
from some_table
order FN_SPECIAL_SORT_KEY(sorted_column,'asc')

The special sort function maps each character to a 2 digit number, and the return value is used for sorting. This seems to be just really expensive concatenation, and it feels wrong.

for i in 1..length(sorted_text)
        loop
            v_result:=v_result ||  case substr(sorted_text,i,1)
                WHEN ' '   THEN 82 WHEN  '!'   THEN 81 WHEN '"'    THEN 80 WHEN  '#'   THEN 79 WHEN  '

I'm having a hard time coming up with an alternative approach.  I want to know what problems exist with this approach.  Perhaps we have no alternatives.

Addendum 1:

Adding example of sorted data.  In general,  all alpha characters case insensitive, then numbers 0-9, then special characters in any order.

Here is a sample sorted ascending list.  Keep in mind special characters are interchangeable, they all should be after letters and numbers.  In binary sort, some special characters are before letters (i.e.  '  )

My desired order,

AB1$

aCC#

ac'

BZ

Oracle binary order

AB1$

BZ

ac'

acc#
                ..............
                WHEN 'u'   THEN 15 WHEN  'U'   THEN 15 WHEN  'v'   THEN 14 WHEN  'V'   THEN 14 WHEN  'w'   THEN 13 WHEN  'W'   THEN 13 WHEN  'x'
                ....
                else 90 end;
        end loop;

I'm having a hard time coming up with an alternative approach. I want to know what problems exist with this approach. Perhaps we have no alternatives.

Addendum 1:

Adding example of sorted data. In general, all alpha characters case insensitive, then numbers 0-9, then special characters in any order.

Here is a sample sorted ascending list. Keep in mind special characters are interchangeable, they all should be after letters and numbers. In binary sort, some special characters are before letters (i.e. ' )

My desired order,

AB1$

aCC#

ac'

BZ

Oracle binary order

AB1$

BZ

ac'

acc#

If the sort order that you want to specify is already supported by Oracle, you can do this by ordering by the NLSSORT function - like so:

ORDER BY NLSSORT(sorted_column, 'NLS_SORT = XDanish') -- Replace XDanish as appropriate

You can find a list of supported sort orders here.

StackExchange Database Administrators Q#9448, answer score: 5