Tony Duan Tony Duan - 1 year ago 63
SQL Question

PostgreSQL count number of times substring occurs in text

I'm writing a PostgreSQL function to count the number of times a particular text substring occurs in another piece of text. For example, calling count('foobarbaz', 'ba') should return 2.

I understand that to test whether the substring occurs, I use a condition similar to the below:

WHERE 'foobarbaz' like '%ba%'

However, I need it to return 2 for the number of times 'ba' occurs. How can I proceed?

Thanks in advance for your help.

Answer Source

How about use a regular expression:

SELECT count(*)
FROM regexp_matches('foobarbaz', 'ba', 'g');

The 'g' flag repeats multiple matches on a string (not just the first).