How to Count comma splitted values having value greater than 0 in pgsql?

I am using PostgreSQL and store values in the database as comma split-ted. Now I want to count all comma split-ted values where individual value is greater than zero.

How can I achieve it?

My db column values are looking like

0,120,0,0,118,0,0,128,0,123,0,0,0,125,0
192,193,196,195
192,193,196,1950,128,0,123,0,0,

What i tried is:

SELECT case when col='0' then 0 else array_length(regexp_split_to_array(replace(replace(col,'0,',''),',0',''), ','), 1) end 
FROM table

Here problem is it replaces all 0 even it exist in any other value

Note: I am using PostgreSQL 8.4.2

Solution

You need to unnest() the values in the array (essentially converting it into a properly normalized model) and then you can correctly count them:

It's unclear to me if you want to count those non-zero values across all rows in the table or for each row.

To count that across all rows:

select count(*)
from the_table, 
     unnest(string_to_array(the_column, ',')) as x(v) 
where v::int > 0;

If you need to count them for each row, you can do that assuming you have a primary key (or unique) column in the table:

select id, count(*)
from the_table, 
     unnest(string_to_array(the_column, ',')) as x(v) 
where v::int > 0
group by id;

The above assumes there is a column id which is unique.

Edit

For old and unsupported Postgres versions you need to change that to:

select count(*)
from (
  select unnest(string_to_array(the_column, ',')) v
  from the_table
) t  
where v::int > 0

select id, count(*)
from (
  select id, unnest(string_to_array(the_column, ',')) v
  from the_table
) t
where v::int > 0
group by id;