Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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#

share|improve this question

migrated from stackoverflow.com Dec 20 '11 at 10:26

This question came from our site for professional and enthusiast programmers.

    
can you post an example data sorted the way you want it? –  Jack Douglas Dec 20 '11 at 11:19
    
@Jack Douglas You got it. –  Andy Pryor Dec 20 '11 at 15:58
add comment

5 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
    
Since these deal with case and diacritics is there really one that would work in this case? –  Leigh Riffel Dec 20 '11 at 21:26
add comment

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.

share|improve this answer
    
I've brought up this migration on mSO - please contribute your views there if you are willing :-) –  Jack Douglas Dec 21 '11 at 13:02
add comment

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)));
share|improve this answer
    
As Jack Douglas pointed out, the ordering of these results is not predictable with regard to translated special characters. So, although this answer solves the OP's question it may not be useful if you require consistent symbol ordering. –  Leigh Riffel Dec 20 '11 at 21:07
add comment
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.

share|improve this answer
1  
A way to make this solution usable with a (single function) function based index is to concatenate the order by. This gives us regexp_replace(lower(foo), '[^a-z]', '~') || regexp_replace(foo, '[^0-9]', '~') || foo. The problem is that this sorts differently than your original solution. So, it is the altered version that needs this correction, not your original. The sort order can be fixed by changing the second regex, which gives an order by of regexp_replace(lower(foo), '[^a-z]', '~') || regexp_replace(foo, '[^0-9a-zA-Z]', '~') || foo. –  Leigh Riffel Dec 20 '11 at 20:00
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.