Oracle: A query that considers occurrences of all non-alphanumeric characters in a string

Question

What would be the best way to count the occurrences of all non-alphanumeric characters that appear in a row in an Oracle database column.

When I tried to find a solution, I realized that I have a request that was not related to the problem, but I noticed that I could change it in the hope of solving this problem. I came up with this:

SELECT  COUNT (*), SUBSTR(TITLE, REGEXP_INSTR(UPPER(TITLE), '[^A-Z,^0-9]'), 1)
FROM    TABLE_NAME
WHERE   REGEXP_LIKE(UPPER(TITLE), '[^A-Z,^0-9]')
GROUP BY    SUBSTR(TITLE, REGEXP_INSTR(UPPER(TITLE), '[^A-Z,^0-9]'), 1)
ORDER BY COUNT(*) DESC;

This works to find the FIRST non-alphanumeric character, but I would like to count the occurrences in the entire string, and not just in the first case. E. g. currently my query parsing "a (string)" will find one parenthesis, but I need to find one parenthesis and one parenthesis.

Answer 1

, , - PL/SQL-. , - , , ( , Oracle).

- , . :

with d as (
   select '(1(2)3)' as str_value
   from dual)
select char_value, count(*)
from (select substr(str_value,level,1) as char_value
      from d
      connect by level <= length(str_value))
where regexp_instr(upper(char_value), '[^A-Z,^0-9]'), 1) <> 0
group by char_value;

Answer 2

Oracle TRANSLATE, regexp:

select a.*,
       length(translate(lower(title),'.0123456789abcdefghijklmnopqrstuvwxyz','.')) 
from table_name a

Answer 3

Try the following:

SELECT  a.*, LENGTH(REGEXP_REPLACE(TITLE, '[^a-zA-Z0-9]'), '')
FROM    TABLE_NAME a