PL/SQL - Working with a string!

Question

I have a string. Let's say: "abcdea" I have to find out what is the most used letter in this string using a anonymous pl/sql block.

How can I do this?

Answer 1

Create a table

create table charcount ( c char(1) )

Split the string into individual characters and insert them into this table using PL/SQL. The answer is then the first row of:

select c, count(1)
from charcount
group by c
order by count(1) desc

Answer 2

Maybe not perfectly elegant, but still working:

declare  
 str varchar2(6 char) := 'abcdea';  
 alphabet varchar2(26 char) := 'abcdefghijklmnopqrstuvwxyz';  
 most_frequent char := '';  
 max_count INTEGER := 0;  
 v_count INTEGER := 0;  
 checking CHAR := ''; 
begin   
 for i in 1..26 loop
        checking := SUBSTR(alphabet, i, 1);
        for k in 1..length(str) 
        loop
          if SUBSTR(str, k, 1) = checking THEN
            v_count := v_count + 1; 
          end if;
        end loop;
        if v_count > max_count THEN
          max_count := v_count;
          most_frequent := checking;
        end if;
        v_count :=0;   
end loop;   
 dbms_output.put_line('Most frequent letter is "'|| most_frequent||'"'); 
end;

Answer 3

Here's a solution using a couple of SYS types:

declare
  str varchar2(6) := 'abcdea';
  tab AWRDRPT_TEXT_TYPE_TABLE := AWRDRPT_TEXT_TYPE_TABLE();
begin
  for i in 1..length(str) loop
     tab.extend(1);
     tab(i) := AWRDRPT_TEXT_TYPE (substr(str,i,1));
  end loop;
  for r in (select output, count(*) as cnt
            from table(tab)
            group by output
            order by cnt desc)
  loop
     dbms_output.put_line(r.output);
     exit;
  end loop;
end;

Answer 4

An associative array is very handy for storing the counts:

DECLARE
    TYPE t_counts IS TABLE OF PLS_INTEGER INDEX BY VARCHAR2(1);
    --
    v_counts t_counts;
    v_string VARCHAR2(100) := 'the quick brown fox jumped over the lazy dog';
    v_char   VARCHAR2(1);
BEGIN
    FOR i IN 1 .. length( v_string ) LOOP
        v_char := substr( v_string, i, 1 );
        IF NOT v_counts.exists( v_char ) THEN
            v_counts( v_char ) := 0;
        END IF;
        v_counts( v_char ) := v_counts( v_char ) + 1;
    END LOOP;
END;
/

Finding the index in the array with the highest count would be trivial to add