CODEWITHSUNDEEP
postgresqlpostgresql-9.4
user47143
user47143

Reputation: 75

PostgreSQL count frequency of each keyword in keyword match

Basically I'm checking to see if a column contains any of a list of keywords in an array 

SELECT COUNT(*) FROM table WHERE value ILIKE ANY (ARRAY['%bat%', '%cat%', ...])

Which works fine to give me the total count of columns with those keywords, but I also want to count how many times each of those keywords is hit. Ideally I would end up with 

 Keyword   |   Count
---------------------
 bat       |     4
 cat       |    10

et cetera. Any ideas? Thanks!

Upvotes: 3

Views: 290

Answers (1)

Ezequiel Tolnay
Ezequiel Tolnay

Reputation: 4582

You'll be better off with regular expressions for testing full words within a text:

with
  words(word) as (values ('bat'), ('cat'))

select w.word, count(*)
from words w
join table t on (t.value ~* ('\m'||w.word||'\M'))
group by w.word;

Replace ~* with ~ if you need case-sensitive searches.

Upvotes: 2

Related Questions