Oracle sort varchar2 column with special characters last

Question

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#

Answer 1

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.

Answer 2

Some options:

1. Persist the sorted version of your data to a table via trigger, and use it.

2. Use Oracle Locale Builder to build a custom sort order. (Caveat: I have never used this, so I do not know what gotchas may exist there.) You could then use the NLSSORT function with that custom sort order.

Answer 3

Another approach is to add a function-based index on FN_SPECIAL_SORT_KEY(sorted_column,'asc'). Avoids the need for an extra column+trigger, and you won't need to modify your queries.

Answer 4

Based on your description it seems like TRANSLATE can do the work for you. As Jeffrey Kemp suggests, a function based index could be created for this.

Setup:

drop table t1;

create table t1 as (
   select 'AB$$' c1 from dual
   union all select 'AB1$' from dual
   union all select 'ABz$' from dual
   union all select 'BZ'   from dual
   union all select 'ac''' from dual
   union all select 'acc#' from dual
   union all select 'aCC#' from dual
);

Demonstration:

select * from t1 order by c1;

SELECT c1 FROM t1 
ORDER BY translate(c1
  ,'abcdefghijklmnopqrstuvwxyz0123456789`-=[]\;'',./~!@#$%^&*()_+{}|:"<>?'
  ,'ABCDEFGHIJKLMNOPQRSTUVWXYZ' || rpad(chr(123),10,chr(123)) 
     || rpad(chr(124),31,chr(124)));

Output:

C1 
----
AB$$ 
AB1$ 
ABz$ 
BZ   
aCC# 
ac'                                       '(For Syntax Highlighter)
acc#   
 7 rows selected 

C1 
----
ABz$ 
AB1$ 
AB$$ 
aCC# 
acc# 
ac'  
BZ       
 7 rows selected 

Check the order of all characters:

SELECT 32+level Value, CHR(32 + level), ascii(CHR(32 + level)) CV FROM dual 
CONNECT BY level <= 255-32 
ORDER BY TRANSLATE(CHR(32 + level)
   , 'abcdefghijklmnopqrstuvwxyz0123456789`-=[]\;'',./~!@#$%^&*()_+{}|:"<>?'
   , 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' || rpad(chr(123),10,chr(123)) 
       || rpad(chr(124),31,chr(124)));

Answer 5

with w as ( select 'AB1$' as foo from dual
  union all select 'aCC#' from dual
  union all select 'ac' from dual
  union all select 'BZ' from dual
  union all select '1' from dual
  union all select 'a' from dual
  union all select '!' from dual )
select foo
from w
order by regexp_replace(lower(foo), '[^a-z]', '~'), regexp_replace(foo, '[^0-9]', '~'), foo;
/*
FOO  
---- 
a    
AB1$ 
ac   
aCC# 
BZ   
1    
!    
*/

If you would like to index the data to avoid a sort in a query with an order by, you can do so like this:

create table bar(foo varchar(100) not null, 
                 foo_o1 as (substr(regexp_replace(lower(foo), '[^a-z]', '~'),1,100)), 
                 foo_o2 as (substr(regexp_replace(foo, '[^0-9]', '~'),1,100)));
create index bar_i on bar (foo_o1, foo_o2, foo);
insert into bar(foo)
select 'AB1$' as foo from dual
union all select 'aCC#' from dual
union all select 'ac' from dual
union all select 'BZ' from dual
union all select '1' from dual
union all select 'a' from dual
union all select '!' from dual;
commit;

explain plan for select foo_o1 from bar order by foo_o1, foo_o2, foo;
select * from table(dbms_xplan.display);
/*
--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     7 |  1092 |     1   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | BAR_I |     7 |  1092 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------
*/

-- edit

As @Leigh has commented, an alternative, neater, approach is to have a single function concatenating the (modified) regexs: regexp_replace(lower(foo), '[^a-z]', '~')||regexp_replace(foo, '[^a-zA-Z0-9]', '~')||foo

including the ||foo on the end in either case makes the ordering deterministic (repeatable) which might be a good thing even though the question doesn't specifically ask for it.