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

by

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

More Related Contents:

  1. Sort by column ASC, but NULL values first?
  2. Using an Alias column in the where clause in Postgresql
  3. Grouped LIMIT in PostgreSQL: show the first N rows for each group?
  4. Possible to perform cross-database queries with PostgreSQL?
  5. Reset auto increment counter in postgres
  6. Optimize query with OFFSET on large table
  7. How to concatenate columns in a Postgres SELECT?
  8. How to use (install) dblink in PostgreSQL?
  9. updating table rows in postgres using subquery
  10. How can I send email from PostgreSQL trigger?
  11. Find overlapping date ranges in PostgreSQL
  12. PostgreSQL multi INSERT…RETURNING with multiple columns
  13. How do you do date math that ignores the year?
  14. “Operator does not exist: integer =?” when using Postgres
  15. Extract date (yyyy/mm/dd) from a timestamp in PostgreSQL
  16. Oracle equivalent of Postgres’ DISTINCT ON?
  17. Postgres sql insert query syntax error from phpPgAdmin
  18. postgresql generate sequence with no gap
  19. PostgreSQL: Case insensitive string comparison
  20. Bulk/batch update/upsert in PostgreSQL
  21. Grant privileges for a particular database in PostgreSQL
  22. Functions with variable number of input parameters
  23. Timestamp Difference In Hours for PostgreSQL
  24. Check if NULL exists in Postgres array
  25. Generate DEFAULT values in a CTE UPSERT using PostgreSQL 9.3
  26. Return rows from INSERT with ON CONFLICT without needing to update
  27. PostgreSQL nested INSERTs / WITHs for foreign key insertions
  28. Insert data and set foreign keys with Postgres
  29. Fetch records that are non zero after the decimal point in PostgreSQL
  30. How to use a ring data structure in window functions

Leave a Comment