CODEWITHSUNDEEP
sqlplsql
lokesh garlapati
lokesh garlapati

Reputation: 11

Display count of characters repeated in a string using SQL only

I want to achieve below code snippet o/p using select query alone. Is it possible without using regexp?

    Character Count when string input is dynamic.

    DECLARE
      str     VARCHAR2(255);
      lv_val  NUMBER;
      lv_char CHAR(1);
      lv_unq  VARCHAR2(255);
    BEGIN
      str:= :p_string;
      FOR i IN 1..length(str)
      LOOP
        lv_val  := 0;
        lv_char := SUBSTR(str,i,1);
        IF instr(lv_unq,lv_char)>0 THEN
          NULL;
        ELSE
          lv_unq := lv_unq||lv_char;
          lv_val := ((LENGTH(str) - LENGTH(REPLACE(replace(str,' ',''), lv_char, ''))) / LENGTH(lv_char));
          --select  ((length(str) - LENgth(REPLACE(str, lv_char, ''))) / LENgth(lv_char)) into lv_val FROM dual;
          DBMS_OUTPUT.PUT_LINE('Character '||lv_char || ' is repeated :'||lv_val||' times in the string '||str);
        END IF;
      END LOOP;
    END;

Upvotes: 1

Views: 963

Answers (1)

Sylvain Leroux
Sylvain Leroux

Reputation: 51990

Answering to the question's title:

Display count of characters repeated in a string using SQL only

with v as (select substr('hello world', level, 1) c from dual connect by level < 12),
     d as (select chr(ascii('a')+level-1) c from dual connect by level <= 26)

select d.c, count(v.c) from d left join v on d.c = v.c
group by d.c
order by d.c;

See http://sqlfiddle.com/#!4/d41d8/38321/0 for the result

The first view split your string into characters. The second view is just the alphabet. Once you have both views, you only need a simple left join with a group by clause to count the number of matching occurrences.

Please note:

  • in the first view, the string and its length are hard-coded in this example
  • I assume all your characters are lower-case
  • I only take into account the 26 (lower case) letters of the ASCII encoding.

Upvotes: 1

Related Questions