Postgresql - Count number of instances of substring in results of ILIKE query

user_10453550 0
Viewed 199

I have a query like this, that returns rows of text that match the given query:

SELECT content 
FROM messages 
  WHERE created_at >= '2021-07-24T20:17:18.141Z'
        AND created_at <= '2021-07-31T20:11:20.542Z'
        AND content ILIKE '%Search Term%';

Right now, if I just count the rows, it just returns the number of messages with that search term. However, I'd like to count the number of Instances of the search term, rather than the number of Rows that contain the search term.

I thought of making a stored function that looped through the results of the above function and counted the instances. But it ended up being insanely slow. I’m okay with it being pretty slow, but is there a solution to this that either runs slightly faster or doesn’t require a function?

postgresql sql
3 Answers

You can use regexp_matches():

select count(*)
from messages m cross join lateral
     regexp_matches(m.content, $search_string, 'g')

Note: This assumes that $search_string doesn't contain regular expression special characters.

If you want the count on each row, you can use:

select m.*,
       (select count(*) 
        from regexp_matches(m.content, $search_string, 'g')
       ) as num_matches
from messages m;
user_11440350

Try this:

SELECT
  SUM(
    CASE
      WHEN content ILIKE '%Search Term%' THEN 1
      ELSE 0
    END
  )
FROM
  messages 
WHERE
  created_at >= '2021-07-24T20:17:18.141Z' AND
  created_at <= '2021-07-31T20:11:20.542Z';
user_33504280

Now I understood your question properly. You can use regexp_matches() in substring to get what you want.

select content,(SELECT count(*) FROM regexp_matches(content, 'Search Term', 'g')) search_count 
FROM messages 
  WHERE created_at >= '2021-07-24T20:17:18.141Z'
        AND created_at <= '2021-07-31T20:11:20.542Z'
        AND content ILIKE '%Search Term%';

** The 'g' flag repeats multiple matches on a string.

user_86516010
Related