Count a pattern of string in PostgreSQL

Question

I have a column named source_id That has records like

12334xxx45566
33445xxx5566, 12344xxx4456
22487xxx5234, 98776xxx6789, 34455xxx7828

Output I am expecting, is the counts of 'xxx' in each row

1
2
3

Answer 1

You can use regexp_matches() which returns each match as a row:

select source_id, (select count(*) from regexp_matches(source_id, 'xxx', 'g')) as counts
from the_table;