Question

2738. Count Occurrences in Text

中文文档

Description

Table: Files

+-------------+---------+
| Column Name | Type  |
+-- ----------+---------+
| file_name   | varchar |
| content   | text  |
+-------------+---------+
file_name is the column with unique values of this table. 
Each row contains file_name and the content of that file.

Write a solution to find the number of files that have at least one occurrence of the words 'bull' and 'bear' as a standalone word, respectively, disregarding any instances where it appears without space on either side (e.g. 'bullet', 'bears', 'bull.', or 'bear' at the beginning or end of a sentence will not be considered) 

Return _the word 'bull' and 'bear' along with the corresponding number of occurrences in any order._

The result format is in the following example.

 

Example 1:

Input: 
Files table:
+------------+----------------------------------------------------------------------------------+
| file_name  | content                                     | 
+------------+----------------------------------------------------------------------------------+
| draft1.txt | The stock exchange predicts a bull market which would make many investors happy. | 
| draft2.txt | The stock exchange predicts a bull market which would make many investors happy, |
|        | but analysts warn of possibility of too much optimism and that in fact we are  |
|        | awaiting a bear market.                              | 
| draft3.txt | The stock exchange predicts a bull market which would make many investors happy, |
|        | but analysts warn of possibility of too much optimism and that in fact we are  |
|        | awaiting a bear market. As always predicting the future market is an uncertain   |
|      | game and all investors should follow their instincts and best practices.     | 
+------------+----------------------------------------------------------------------------------+
Output: 
+------+-------+
| word | count |  
+------+-------+
| bull | 3   | 
| bear | 2   | 
+------+-------+
Explanation: 
- The word "bull" appears 1 time in "draft1.txt", 1 time in "draft2.txt", and 1 time in "draft3.txt". Therefore, the total number of occurrences for the word "bull" is 3.
- The word "bear" appears 1 time in "draft2.txt", and 1 time in "draft3.txt". Therefore, the total number of occurrences for the word "bear" is 2.

Solutions

Solution 1

# Write your MySQL query statement below
SELECT 'bull' AS word, COUNT(*) AS count
FROM Files
WHERE content LIKE '% bull %'
UNION
SELECT 'bear' AS word, COUNT(*) AS count
FROM Files
WHERE content LIKE '% bear %';