sql - PostgreSQL count number of times substring occurs in text

Question

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 1

I would highly suggest checking out this answer I posted to "How do you count the occurrences of an anchored string using PostgreSQL?". The chosen answer was shown to be massively slower than an adapted version of regexp_replace(). The overhead of creating the rows, and the running the aggregate is just simply too high.

The fastest way to do this is as follows...

SELECT
  (length(str) - length(replace(str, replacestr, '')) )::int
  / length(replacestr)
FROM ( VALUES
  ('foobarbaz', 'ba')
) AS t(str, replacestr);

Here we

1. Take the length of the string, L1
2. Subtract from L1 the length of the string with all of the replacements removed L2 to get L3 the difference in string length.
3. Divide L3 by the length of the replacement to get the occurrences

For comparison that's about five times faster than the method of using regexp_matches() which looks like this.

SELECT count(*)
FROM ( VALUES
  ('foobarbaz', 'ba')
) AS t(str, replacestr)
CROSS JOIN LATERAL regexp_matches(str, replacestr, 'g');