Skip to content

Instantly share code, notes, and snippets.

@mhkeller

mhkeller/count-string-occurrences.sql

Created October 15, 2019 19:31
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save mhkeller/b3c4e11815038e57addaf471435aed7d to your computer and use it in GitHub Desktop.
Save mhkeller/b3c4e11815038e57addaf471435aed7d to your computer and use it in GitHub Desktop.
Download ZIP
count occurrences of a string across rows of a pgsql table
Raw
count-string-occurrences.sql
--useful for seeing if a css class exists across all instances of scraped pages in a pgsql database
--adapted from here https://stackoverflow.com/questions/36376410/counting-the-number-of-occurrences-of-a-substring-within-a-string-in-postgresql
select sum(array_length(string_to_array(html, 'SELECTOR'), 1) - 1) from TABLE_NAME
--should equal rows in the table
--or this to get occurrences per row
select (array_length(string_to_array(html, 'SELECTOR'), 1) - 1) as ct from TABLE_NAME ORDER BY ct DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment