I want to count the number of occurences of a value in a string

Question

Given a column namely a which is a result of array_to_string(array(some_column)), how do I count an occurrence of a value from it?

Say I have '1,2,3,3,4,5,6,3' as a value of a column.
How do I get the number of occurrences for the value '3'?

if `a` is the result of `array_to_string(array(some_column))` why not just `count(CASE WHEN some_column=3 THEN 1 END)` — Evan Carroll, Mar 10 '17 at 02:22
Possible duplicate of [PostgreSQL count number of times substring occurs in text](http://stackoverflow.com/questions/25757194/postgresql-count-number-of-times-substring-occurs-in-text) — Evan Carroll, Mar 10 '17 at 02:26

score 1 · Accepted Answer · answered Apr 28 '15 at 04:22

1

I solved it myself. Thank you for all the ideas!

SELECT count(something)
FROM unnest(
        string_to_array(
            '1,2,3,3,4,5,6,3'
        , ',')
    ) something
WHERE something = '3'

answered Apr 28 '15 at 04:22

Kevin

327
6
17

1

This should also be the accepted answer, since it's superior. – Erwin Brandstetter Apr 28 '15 at 04:25

bpinhosilva · Answer 2 · 2015-04-28T04:07:33.777

0

It seems you need to use unnest.

Try this:

select idTable, (select sum(case x when '3' then 1 else 0 end) 
from unnest(a) as dt(x)) as counts 
from yourTable;

edited Apr 28 '15 at 04:07

answered Apr 28 '15 at 03:30

bpinhosilva

692
1
5
15

score 0 · Answer 3 · answered Apr 28 '15 at 04:19

0

why not just create a for loop() and increment a variable i within an if/else statement for whenever a value == 3

for(var i =0; i<intArray.length;i++){
     if(int[i] = 3){
     var j += 1;

} }

something like that, hopefully you get the idea.

answered Apr 28 '15 at 04:19

CodeArt

15
5

This answer seems misplaced. The procedural approach is no match for a set-based solution in SQL. – Erwin Brandstetter Apr 28 '15 at 06:42

score 0 · Answer 4 · edited Apr 13 '17 at 12:42

Based on my "How do you count the occurrences of an anchored string using PostgreSQL?"

The fastest way to count occurrences in a sub-string is this.

SELECT length(data) - length(replace(data, '3', ''))
  / length('3')
FROM foo;

If you're creating the string with array_to_string, it makes sense to unnest, but it won't be faster. Moreover, I would love to see the full query.

count(CASE WHEN some_column=3 THEN 1 END)

sounds the fastest, or if you upgrade to a new version of PostgreSQL..

count(*) FILTER (WHEN some_column=3)

I want to count the number of occurences of a value in a string

4 Answers4