pl/sql function to order string from varchar

user_19663383 0
Viewed 38

function that take two parameters, the first to be a string and the second is the order (Asc or Desc) and the returned output to be ordering the first string as per the second parameter.

IN : dgtak OUT: adgkt

Tried this but doesn't seem to work

CREATE OR REPLACE FUNCTION order_string(my_string IN VARCHAR2)
  RETURN VARCHAR2 IS
  ret_string VARCHAR2(4000);
BEGIN
  SELECT LISTAGG(regexp_substr(my_string, '\w', 1, level), '') WITHIN
   GROUP(
   ORDER BY 1)
    INTO ret_string
    FROM dual
  CONNECT BY regexp_substr(my_string, '\w', 1, level) IS NOT NULL;
  RETURN ret_string;
END;

select order_string('dgtak') as RESULT from dual;
plsql oracle sql
2 Answers

Here's one option:

SQL> create or replace function order_string (par_string in varchar2, par_order in varchar2)
  2  return varchar2
  3  is
  4    retval varchar2(100);
  5  begin
  6    with temp (val) as
  7      -- split PAR_STRING to rows
  8      (select substr(par_string, level, 1)
  9       from dual
 10       connect by level <= length(par_string)
 11      )
 12    -- aggregate characters back in ascending or descending order
 13    select case when par_order = 'Asc'  then listagg(val, '') within group (order by val asc)
 14                when par_order = 'Desc' then listagg(val, '') within group (order by val desc)
 15                else null
 16           end
 17      into retval
 18      from temp;
 19
 20    return retval;
 21  end;
 22  /

Function created.

Testing:

SQL> select order_string('dfag', 'Asc')  result_asc,
  2         order_string('dfag', 'Desc') result_desc
  3  from dual;

RESULT_ASC           RESULT_DESC
-------------------- --------------------
adfg                 gfda

SQL>
user_90979060

Just for fun, here's a procedural version. It has more lines of code than the SQL version but in my tests it's slightly faster.

create or replace function order_string
    ( p_string varchar2
    , p_reverse varchar2 default 'N' )
    return varchar2
as
    pragma udf;
    type letter_tt is table of number index by varchar2(1);
    letters letter_tt := letter_tt();
    letter varchar2(1);
    sorted_string long;
    string_length integer := length(p_string);
begin
    -- Store all characters of p_string as indices of array:
    for i in 1..string_length loop
        letter := substr(p_string,i,1);

        if letters.exists(letter) then
            letters(letter) := letters(letter) +1;
        else
            letters(letter) := 1;
        end if;
    end loop;

    -- Loop through array appending each array index to sorted_string
    for i in indices of letters loop
        for r in 1..letters(i) loop
            sorted_string := sorted_string || i;
        end loop;
    end loop;
    
    if p_reverse = 'Y' then
        select reverse(sorted_string) into sorted_string from dual;
    end if;

    return sorted_string;
end order_string;

I've used the 21c indices of loop iterator, but you can write a conventional loop in earlier versions. You might also use two alternative loops for ascending and descending order in place of my hack.

user_2304710
Related