Sort chars in varchar2 alphabetically

Question

I'm looking for a function that would sort chars in varchar2 alphabetically.

Is there something built-in into oracle that I can use or I need to create custom in PL/SQL ?

Answer 1

From an answer at http://forums.oracle.com/forums/thread.jspa?messageID=1791550 this might work, but don't have 10g to test on...

SELECT MIN(permutations)
FROM (SELECT REPLACE (SYS_CONNECT_BY_PATH (n, ','), ',') permutations
    FROM (SELECT LEVEL l, SUBSTR ('&col', LEVEL, 1) n
        FROM DUAL
        CONNECT BY LEVEL <= LENGTH ('&col')) yourtable
    CONNECT BY NOCYCLE l != PRIOR l)
WHERE LENGTH (permutations) = LENGTH ('&col')

In the example col is defined in SQL*Plus, but if you make this a function you can pass it in, or could rework it to take a table column directly I suppose.

I'd take that as a start point rather than a solution; the original question was about anagrams so it's designed to find all permutations, so something similar but simplified might be possible. I suspect this doesn't scale very well for large values.

Answer 2

Assuming you don't mind having the characters returned 1 per row:

select substr(str, r, 1) X from (
select 'CAB' str,
       rownum r
from dual connect by level <= 4000
) where r <= length(str) order by X;

X
=
A
B
C

Answer 3

You should remember that there is no common agreement what "alphabetically" means. It all depends on which country it is, and who is looking at your data and what context it is in.

For instance in DK, there are a large number of different sortings of a,aa,b,c,æ,ø,å

• per the alphabet: a,aa,b,c,æ,ø,å
• for some dictionary: a,aa,å,b,c,æ,ø
• for other dictionaries: a,b,c,æ,ø,aa,å
• per Microsoft standard: a,b,c,æ,ø,aa,å

check out http://blogs.msdn.com/michkap/archive/2006/04/27/584439.aspx for more info. Which also happens to be a great blog for issues as these.

Answer 4

So eventually I went PL/SQL route, because after searching for some time I realized that there is no build-in function that I can use.

Here is what I came up with. Its based on the future of associative array which is that Oracle keeps the keys in sorted order.

create or replace function sort_chars(p_string in varchar2) return varchar deterministic
as
     rv varchar2(4000);
     ch  varchar2(1);
     type vcArray is table of varchar(4000) index by varchar2(1);
     sorted vcArray;

     key varchar2(1);

begin
     for i in 1 .. length(p_string)
     loop
        ch := substr(p_string, i, 1);

        if (sorted.exists(ch))
        then 
            sorted(ch) := sorted(ch) || ch;
        else
            sorted(ch) := ch;
        end if;
     end loop;


    rv := '';
    key  := sorted.FIRST;
    WHILE key IS NOT NULL LOOP
        rv := rv || sorted(key);
        key := sorted.NEXT(key);
    END LOOP;

     return rv;
end;

Simple performance test:

set timing on;

create table test_sort_fn as 
select t1.object_name || rownum as test from user_objects t1, user_objects t2;

select count(distinct test) from  test_sort_fn;

select count (*)  from (select sort_chars(test)  from test_sort_fn);


Table created.
Elapsed: 00:00:01.32

COUNT(DISTINCTTEST)
-------------------
             384400
1 row selected.
Elapsed: 00:00:00.57

  COUNT(*)
----------
    384400
1 row selected.
Elapsed: 00:00:00.06