Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2001
    Posts
    80

    Unanswered: Function to count the specific character

    Is there any function to count the number of specific character for a string? For example, if I want to count the character ^.

    String 1 : Jo^h^n = 2
    String 2 : P^e^t^e^r = 4
    String 3 : J^o^h^n = 3

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I can't remember if there is an original Oracle function to perform such a count, but you might write one by yourself (such as this one):
    Code:
    CREATE OR REPLACE FUNCTION fun_cnt_char (par_string IN CHAR, par_char IN CHAR)
    RETURN NUMBER
    IS
       retval NUMBER := 0;
    BEGIN
      FOR i IN 1 .. LENGTH(par_string) LOOP
        IF SUBSTR(par_string, i, 1) = par_char
        THEN
           retval := retval + 1;
        END IF;
      END LOOP;
      RETURN (retval);
    END ;
    /
    And the result is
    Code:
    SQL> SELECT fun_cnt_char (' P^e^t^e^r', '^') FROM dual;
    
    FUN_CNT_CHAR('P^E^T^E^R','^')
    -----------------------------
                                4
    
    SQL>

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Sure, using the length and replace function it is easy

    SQL> select length(' P^e^t^e^r') - length(replace(' P^e^t^e^r','^')) from dual;

    LENGTH('P^E^T^E^R')-LENGTH(REPLACE('P^E^T^E^R','^'))
    ----------------------------------------------------
    4

    SQL>
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •