Sort String in PLSQL

I will develop PLSQL function which sorts string regarding ACII code of characters
This function uses a lot of intelligence to sort strings.

Idea of Sorting
I depend on create PLSQL table indexed by BINARY_INTEGER which its index mapped to ASCII code of every character in my string and I store in table number of occurrence per every character.


I also created PLSQL RECORD that consists of two elements
1- letter : character in my string
2- Seq  :  Number of repeating character in string

After Storing character in PLSQL RECORD and added it to PLSQL TABLE, I read it again from PLSQL TABLE as it was sorted regarding ASCII code in PLSQL TABLE.

You can debug it and know how it is executed.



CREATE OR REPLACE FUNCTION sort_string (p$string IN VARCHAR2)
   RETURN VARCHAR2
IS
   TYPE typ_rec IS RECORD (letter VARCHAR2 (256), seq 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 (p$string)
   LOOP
      tabr (ASCII (SUBSTR (p$string, i, 1))).letter := SUBSTR (p$string, i, 1);
      tabr (ASCII (SUBSTR (p$string, i, 1))).seq :=
         NVL (tabr (ASCII (SUBSTR (p$string, i, 1))).seq, 0) + 1;
   END LOOP;

   FOR i IN tabr.FIRST .. tabr.LAST
   LOOP
      IF tabr.EXISTS (i)
      THEN
         LC$Result :=
            LC$Result
            || RPAD (tabr (i).letter, tabr (i).seq, tabr (i).letter);
      END IF;
   END LOOP;

   RETURN LC$Result;
END;

We can now call function in PLSQL


DECLARE
   lc$string   VARCHAR2 (1024);
BEGIN
   lc$string := sort_string ('mahmoud');
   DBMS_OUTPUT.put_line (lc$string);
END;

OR

SELECT sort_string ('mahmoud') FROM DUAL:

The result will be

adhmmou

If you have any confusion about this using trick , don't hesitate to contact me.

Thanks
Mahmoud Ahmed El-Sayed

Popular posts from this blog

ADF : Get Current Logged User Name

OAF : Get Current Row in Table

ADF : Working with ViewCriteria