How to find count and names of distinct characters in string in PL/SQL [duplicate]

You can use regular expression as follows:

SET serveroutput ONDECLARE     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

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