How to Count the Number of Occurrences of a Character in an Oracle Varchar Value

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

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).

Oracle: counting the number of substrings in a string?

As of version 11g, regexp_count will do this.

select regexp_count('abba', 'b') from dual;
   2 
select regexp_count('abba', 'b+') from dual;
   1 

Finding the count of characters and numbers in a string

If I understand correctly you are using Oracle PLSQL, and as far as I know, there isn't any "built-in" method (in PLSQL) that counts the number of digits/characters in a string.

But, you can do the following to count characters:

select LENGTH(REGEXP_REPLACE('abcd12345','[0-9]')) from dual

and digits:

select LENGTH(REGEXP_REPLACE('abcd12345','[a-zA-Z]')) from dual

Or, in your case:

select name,
LENGTH(REGEXP_REPLACE(name,'[a-zA-Z]','')) as num_count,
LENGTH(REGEXP_REPLACE(name,'[0-9]','')) as char_count,
from test6;

For Bill the Lizard:
My answer was tested on Oracle 11g and it works just fine!

If you decide to delete my answer again, please be kind enough to add a comment that explains why. I was also looking for you in the chat rooms...

Oracle / SQL - Count number of occurrences of values in a single column

Use:

  SELECT t.code,
         COUNT(*) AS numInstances
    FROM YOUR_TABLE t
GROUP BY t.code

The output will resemble:

code   numInstances
--------------------
A      3
B      5
C      1

If a code exists that has not been used, it will not show up. You'd need to LEFT JOIN to the table containing the list of codes in order to see those that don't have any references.

ORACLE - How to count the number of occurrences of a member in a collection?

If you wanted to avoid duplicates entirely, you could use an associative array. Something like:

DECLARE
    TYPE v_type IS TABLE OF number index by varchar2(4000);
    v_coll v_type;
    idx varchar2(4000);
BEGIN
    v_coll('not dupe'):= 1;
    v_coll('dupe'):= 1;
    v_coll('dupe'):= 1;

    -- show key values 
    idx := v_coll.first;
    loop
      exit when idx is null;
      dbms_output.put_line(idx);
      idx := v_coll.next(idx);
    end loop;

END;

Output:

dupe
not dupe

how to count dot number in a string in oracle

You need to escape . -- and to add 1:

SELECT REGEXP_COUNT('3.222.123.44.1055', '[.]') + 1
FROM dual; 

How to count instances of character in SQL Column

In SQL Server:

SELECT LEN(REPLACE(myColumn, 'N', '')) 
FROM ...

How to make use of SQL (Oracle) to count the size of a string?

You can use LENGTH() for CHAR / VARCHAR2 and DBMS_LOB.GETLENGTH() for CLOB. Both functions will count actual characters (not bytes).

See the linked documentation if you do need bytes.

Oracle regex count multiple occurrences of a string surrounded by commas

You can use regexp_count() if you hack the string:

select col1, regexp_count(replace(col1, ',', ',,'), '(^|\W)SL(\W|$)')

This doubles the delimiter so the first match doesn't eat it up -- getting around the underlying issue which is that Oracle regular expressions do not support look-ahead.

Here is a db<>fiddle.

---

Related Topics