Tony Duan Tony Duan - 7 months ago 22
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

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).