CODEWITHSUNDEEP
mysqlsql
Goje87
Goje87

Reputation: 2869

Query to obtain number of occurrence of a given word in a database table

I have the following tables in the database.

users
-----------------
| id | username |
-----------------
|  1 | goje     |
|  2 | john     |
|  3 | henry    |
-----------------

comments
-------------------------------------------------
| id | uid |          comment                   |
-------------------------------------------------
|  1 |  1  | One who works hard is the one who  |
|    |     | succeeds                           |
|  2 |  1  | This one was the best.             |
|  3 |  2  | You are one of my best friends.    |
|  4 |  3  | He was the one who bought this one.|
-------------------------------------------------

Now, lets suppose the given word is "one". I would like to have a MySQL query that can let me know the number of occurrences of this word for each user in the users table. I this case the result should be 

--------------------
| username | count |
--------------------
| goje     | 3     |   -> 2 in first comment and 1 in second
| henry    | 2     |   -> 2 in henry's only comment
| john     | 1     | 
--------------------

Kindly let me know how can I achieve this using SQL. Thanks!!

Upvotes: 1

Views: 312

Answers (1)

berty
berty

Reputation: 2206

Try this query :

SELECT
 users.username AS username,
 round(sum((LENGTH(comments.comment) - LENGTH(REPLACE(LOWER(comments.comment),"one", ""))) / LENGTH("one")), 0) AS count
FROM users
 INNER JOIN comments ON comments.uid = users.id
GROUP BY users.id
ORDER BY count DESC;

I dont know if REGEXP approach is less or more expensive.

Upvotes: 3

Related Questions