How to count the number of occurrences of a character in an Oracle varchar value

Question

How can I count number of occurrences of the character - in a varchar2 string   Example   select XXX  123-345-566    -   from dual  ---------------------------------------- 2

User · Answer

I thought of   SELECT LENGTH  123-345-566   - LENGTH REPLACE  123-345-566    -        FROM DUAL

User · Answer

I justed faced very similar problem    BUT RegExp Count couldn t resolved it  How many times string  16 124 3 3 1 0   contains   3    As we see 2 times  but RegExp Count returns just 1  Same thing is with   bbaaaacc  and when looking in it  aa  - should be 3 times and RegExp Count returns just 2   select REGEXP COUNT  336 14 3 3 11 0       3    from dual  select REGEXP COUNT  bbaaaacc     aa   from dual    I lost some time to research solution on web  Couldn t  find    so i wrote my own function that returns TRUE number of occurance  Hope it will be usefull   CREATE OR REPLACE FUNCTION EXPRESSION COUNT  pEXPRESSION VARCHAR2  pPHRASE VARCHAR2   RETURN NUMBER AS   vRET NUMBER    0    vPHRASE LENGTH NUMBER    0    vCOUNTER NUMBER    0    vEXPRESSION VARCHAR2 4000     vTEMP VARCHAR2 4000   BEGIN   vEXPRESSION    pEXPRESSION    vPHRASE LENGTH    LENGTH  pPHRASE      LOOP     vCOUNTER    vCOUNTER   1      vTEMP    SUBSTR  vEXPRESSION  1  vPHRASE LENGTH       IF  vTEMP   pPHRASE  THEN                 vRET    vRET   1      END IF      vEXPRESSION    SUBSTR  vEXPRESSION  2  LENGTH  vEXPRESSION   - 1     EXIT WHEN   LENGTH  vEXPRESSION     0   OR  vEXPRESSION IS NULL     END LOOP    RETURN vRET  END

User · Answer

You can try this  select count  distinct pos  from  select instr  123-456-789    -   level  as pos from dual   connect by level  lt  length  123-456-789    where nvl pos  0    0   it counts  properly  olso for how many  aa  in  bbaaaacc    select count  distinct pos  from  select instr  bbaaaacc    aa   level  as pos from dual   connect by level  lt  length  bbaaaacc    where nvl pos  0    0

User · Answer

Here s an idea  try replacing everything that is not a dash char with empty string  Then count how many dashes remained   select length regexp replace  123-345-566      -         from dual

User · Answer

Here you go   select length  123-345-566   - length replace  123-345-566   -  null    from dual    Technically  if the string you want to check contains only the character you want to count  the above query will return NULL  the following query will give the correct answer in all cases   select coalesce length  123-345-566   - length replace  123-345-566   -  null    length  123-345-566    0   from dual    The final 0 in coalesce catches the case where you re counting in an empty string  i e  NULL  because length NULL    NULL in ORACLE

User · Answer

select count    from         select substr  K u n a l  level 1  str       from dual       connect by level  lt  length  K u n a l          where str

User · Answer

here is a solution that will function for both characters and substrings   select  length  a   - nvl length replace  a   b    0     length  b     from dual   where a is the string in which you search the occurrence of b  have a nice day

User · Answer

REGEXP COUNT should do the trick   select REGEXP COUNT  123-345-566    -   from dual

User · Answer

SELECT  FN LENGTH  123-345-566    -  FN LENGTH  FN REPLACE  123-345-566               FROM DUAL

[sql] How to count the number of occurrences of a character in an Oracle varchar value?

Examples related to sql

Examples related to oracle

Examples related to count

Examples related to oracle10g