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

by

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

More Related Contents:

  1. Merging two tables in SQL [closed]
  2. Is it possible to output a SELECT statement from a PL/SQL block?
  3. Select count(*) from multiple tables
  4. Pivoting rows into columns dynamically in Oracle
  5. Using ‘case expression column’ in where clause
  6. Oracle Joins – Comparison between conventional syntax VS ANSI Syntax
  7. Concatenate results from a SQL query in Oracle
  8. How to use BOOLEAN type in SELECT statement
  9. How to kill a running SELECT statement
  10. Selecting the second row of a table using rownum
  11. Update with Join query in Oracle
  12. Different CURRENT_TIMESTAMP and SYSDATE in oracle
  13. Oracle date “Between” Query
  14. ORA-01861: literal does not match format string
  15. Oracle Date TO_CHAR(‘Month DD, YYYY’) has extra spaces in it
  16. Query a Table’s Foreign Key relationships
  17. Is there an Oracle equivalent to SQL Server’s OUTPUT INSERTED.*?
  18. SQL multiple columns in IN clause
  19. Does Oracle store trailing zeroes for Number data type?
  20. Oracle Update Query using Join
  21. Autoincrement in oracle to already created table
  22. What can cause an Oracle ROWID to change?
  23. Oracle query to match all values in the list among all rows in table
  24. How do NULL values affect performance in a database search?
  25. Check if two “select”s are equivalent
  26. Query for count of distinct values in a rolling date range
  27. “column not allowed here” error in INSERT statement
  28. Concat function is not working – invalid number of arguments
  29. ORACLE After update trigger: solving ORA-04091 mutating table error
  30. Oracle IN vs Exists difference?

Leave a Comment