Counting the Number of Occurrences of a Substring Within a String in Postgresql

Counting the number of occurrences of a substring within a string in PostgreSQL

A common solution is based on this logic: replace the search string with an empty string and divide the difference between old and new length by the length of the search string

(CHAR_LENGTH(name) - CHAR_LENGTH(REPLACE(name, 'substring', ''))) 
/ CHAR_LENGTH('substring')

Hence:

UPDATE test."user"
SET result = 
    (CHAR_LENGTH(name) - CHAR_LENGTH(REPLACE(name, 'o', ''))) 
    / CHAR_LENGTH('o');

PostgreSQL count number of times substring occurs in text

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');

PostgreSQL SQL query to find number of occurrences of substring in string

This is easily done without a custom function:

select count(*)
from (values ('Earth is my home planet and where my friends live')) v(str) cross join lateral
     regexp_split_to_table(v.str, ' ') word join
     patterns p
     on word = p.pattern

Just break the original string into "words". Then match on the words.

Another method uses regular expression matching:

select (select count(*) from regexp_matches(v.str, p.rpattern, 'g'))
from (values ('Earth is my home planet and where my friends live')) v(str) cross join
     (select string_agg(pattern, '|') as rpattern
      from patterns
     ) p;

This stuffs all the patterns into a regular expression. Not that this version does not take word breaks into account.

Here is a db<>fiddle.

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

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';

How to separate and count string from TEXT column PostgreSQL

The following counts the number of attachments that appear in the list:

select unnest(regexp_split_to_array(attachment_name, '[\^]')) as attachment, count(*)
from clients
group by attachment;

EDIT:

I answered the wrong question.

If you want only one attachment, then something like this:

select count(*)
from clients c
where attachment_name not like '^%^%'

How do you count the number of occurrences of a certain substring in a SQL varchar?

The first way that comes to mind is to do it indirectly by replacing the comma with an empty string and comparing the lengths

Declare @string varchar(1000)
Set @string = 'a,b,c,d'
select len(@string) - len(replace(@string, ',', ''))

Counting the Number of Occurrences of a Multi-Word Phrase in Text with PostgreSQL

If the words are contained just once in the string (I am supposing here that your table contains two columns, one with an id and another with a text column called my_text):

SELECT
     count(id)
FROM
     my_table
WHERE
     my_text ~* 'the_words_i_am_looking_for'

If the occurrences are more than one per field, this nested query can be used:

SELECT
     id,
     count(matches) as matches
FROM (
     SELECT
          id,
          regexp_matches(my_text, 'the_words_i_am_looking_for', 'g') as matches
     FROM
          my_table
) t
GROUP BY 1

The syntax of this function and much more about string pattern matching can be found here.

---

Related Topics