Hi!
I want to count how many times specific words appears in all of our articles. I have 2 tables:
One with the articles that look like this, this is the Recipe table (each recipe is a row) :
Recipe_ID | Title | Summary | Body |
565126 | Chocolate Chip Cookies | The best cookie recipe | With the rack in the middle position, preheat the oven to 180 °C (350 °F). Line a large baking sheet with parchment paper.
In a bowl, combine the flour, baking powder, and salt. Set aside.
In another bowl, cream the butter and brown sugar with an electric mixer. Add the egg and vanilla and beat until smooth. At low speed or with a wooden spoon, stir in the dry ingredients and chocolate.
With a spoon, place 30 ml (2 tablespoons) of dough per cookie on a baking sheet leaving about 5-cm (2-inch) between each. Flatten slightly with your fingertips.
Bake for about 14 minutes. |
And another table that contain the specific words I want to count, named Keywords :
Flavor | Author | Type |
Chocolate | Jane | Salty |
Peanut butter | Richard | Sweet |
Strawberry | Elizabeth | |
Cookie dough | Donald | |
The final output expected should look like this (the number are the times they are repeated across all recipes):
Flavor | |
Chocolate | 5 |
Peanut butter | 4 |
Strawberry | 9 |
Cookie dough | 10 |
| |
Author | |
Jane | 1 |
Richard | 3 |
Elizabeth | 9 |
Donald | 4 |
| |
Type | |
Salty | 3 |
Sweet | 7 |
The goal is to search the articles (recipes) and know how many times we repeat some specific words and know if we are repeating/talking about something too much or not enough. I want to search across all the Title, summary and body (maybe I should merge all three columns first?).
The text might contain html code, so I want to count the word even if it is displayed as “<br>salty<br>” and count the word no matter if it is in upper or lower case. Finally, I would like to be able to count a word starting with a specific part. Like words starting with “salt” to include Salty ( I might have to change my keyword table for that, or maybe there I solution where I don’t need the keyword table since I would need to enter a specific formula for each words?)
How to achieve this? What is the most simple way to do it? Please help me! 🙂
