How to find count and names of distinct characters

Question

This question already has an answer here:

• How can I get the unique characters from a string in Oracle? 1 answer

I'm quite new to PL/SQL and I need to get the names and count of the distinct characters in a string. E.g. if I have a string str="helloexample", I need to get output of distinct characters in str, i.e. heloxamp.

How can I do this?

Answer 1

You can use regular expression as follows:

SET serveroutput ON
DECLARE
     str        VARCHAR2(20):='helloexample';
     str_length NUMBER;
     c          VARCHAR2(20):=NULL;
     d          NUMBER;
BEGIN
     str_length:=LENGTH(str);
     FOR i IN 1..str_length
     LOOP
          IF regexp_instr(c,SUBSTR(str,i,1))>0 THEN
               NULL;
          ELSE
               c:=c||SUBSTR(str,i,1);
          END IF;
     END LOOP;
     dbms_output.put_line(c);
END;

the answer would be:

heloxamp

Answer 2

if WM_CONCAT isnt working for you, you can use this trick:

select rtrim(xmlagg(xmlelement(e, str)).extract('//text()'), ',') as output
  from (select substr(str, level, 1) str, level l,
               row_number() over (partition by substr(str, level, 1) 
                                      order by level) rn
          from (select 'helloexample' str  from dual) d
        connect by level <= length(str)
        order by level)
 where rn = 1;


SQL> select rtrim(xmlagg(xmlelement(e, str)).extract('//text()'), ',') as output
  2    from (select substr(str, rownum, 1) str, level l,
  3                 row_number() over (partition by substr(str, rownum, 1) order by level) rn
  4            from (select 'helloexample' str  from dual) d
  5          connect by level <= length(str)
  6          order by level)
  7   where rn = 1;

OUTPUT
--------------------------------------------------------------------------------
heloxamp