Overblog Suivre ce blog
Editer l'article Administration Créer mon blog

Search

Free tool

Look and Feel Project

9 mai 2006 2 09 /05 /mai /2006 22:09

This a small function that allows to sort the content of a string (ASCII sort).
It use a collection or records to do the job:

CREATE OR REPLACE FUNCTION sort_string(pc$string IN VARCHAR2)
RETURN VARCHAR2
IS
  TYPE typ_rec IS RECORD(lettre VARCHAR2(10), nbre pls_integer);
  TYPE tab_rec IS TABLE OF typ_rec INDEX BY binary_integer;
  tabr tab_rec;
  LC$Result VARCHAR2(32767);
BEGIN

  FOR i IN 1 .. LENGTH(pc$string)
  LOOP
    tabr(ASCII(SUBSTR(pc$string, i, 1))).lettre := SUBSTR(pc$string, i, 1);
    tabr(ASCII(SUBSTR(pc$string, i, 1))).nbre   := NVL(tabr(ASCII(SUBSTR(pc$string, i, 1))).nbre, 0) + 1;
  END LOOP;

  FOR i IN tabr.FIRST .. tabr.LAST
  LOOP

    IF tabr.EXISTS(i) THEN
      LC$Result := LC$Result || rpad(tabr(i).lettre, tabr(i).nbre, tabr(i).lettre);
    END IF;

  END LOOP;

  RETURN LC$Result;
END;


That you can call with the following:

SQL> Begin
  2    dbms_output.put_line(sort_string('Zas95f1g6Az7b3k5a'));
  3  End ;
  4  /
1355679AZaabfgksz

PL/SQL procedure successfully completed.


Here are some links about 2 great articles about Sorting Collection from the AMIS Technology Blog:

Sorting PL/SQL Collections, the hard way, the intermediate way and the quite simple way (part one)
Sorting PL/SQL Collections, the quite simple way (part two: Have the SQL Engine do the heavy lifting)

Partager cet article

Repost 0
Francois Degrelle - dans Oracle PL-SQL
commenter cet article

commentaires

Francois 12/05/2006 14:33

Then SUBSTRC instead of SUBSTRhttp://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions119a.htm#SQLRF06114

Francois 12/05/2006 14:30

Maybe you couls try to replace ASCII by ASCIISTR in the function.

Andrew 10/05/2006 22:07

It seems to have some problem with UTF8 characters - maybe becuase you are using ascii(), length(). It is very slow with in my test.
SQL> create table utf8_test (col1 varchar2(20 char));
Table created.
SQL> -- http://www.unicode.org/Public/3.0-Update/NamesList-3.0.0.txtSQL> -- Euro is U+20AC (226,130,172), Small Greek Gamma U+03B3SQL> insert into utf8_test values ('321'||unistr('\20AC')||unistr('\03B3')||'cab');
1 row created.
SQL> select dump(col1, 1010) from utf8_test;
DUMP(COL1,1010)--------------------------------------------------------------------------------Typ=1 Len=11 CharacterSet=AL32UTF8: 51,50,49,226,130,172,206,179,99,97,98
SQL> select x, dump(x, 1010), dump(y, 1010)  2  from (select sort_string(col1) X, col1 y from utf8_test);
X--------------------------------------------------------------------------------DUMP(X,1010)--------------------------------------------------------------------------------DUMP(Y,1010)--------------------------------------------------------------------------------123abc €Typ=1 Len=10 CharacterSet=AL32UTF8: 49,50,51,97,98,99,32,226,130,172Typ=1 Len=11 CharacterSet=AL32UTF8: 51,50,49,226,130,172,206,179,99,97,98
SQL>