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 '$'
..............
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#
Best Answer
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:
You can find a list of supported sort orders here.