How to count non-zero numbers in a comma separated string in oracle sql

Question

Want to count non-zero numbers from a comma separated string in oracle 12.10. If my column has data as 2000,300,0,0 then count it as 2.

This gives me 2000 and 300 as result but what’s next to count it.

select regexp_substr('2000,300,0,0','[^,]+',1,level) from dual connect BY to_number(regexp_substr('2000,300,0,0', '[^,]+',1,level)) > 0

Thanks for helping me. I know I am missing a basic step and that is haunting me.

Answer

If you have only integers:

select regexp_count('2000,300,0,0', '([1-9]+0*)+') cnt from dual

Accepted Answer

If you have only integers:select regexp_count('2000,300,0,0', '([1-9]+0*)+') cnt from dual