2

For example, say the keywords are "Tokyo restaurant" and I have PlaceDescription column from Place table where its PlaceID=1's PlaceDescription is "XXXXX is a beautiful Tokyo restaurant." and PlaceID=2's PlaceDescription is "YYYYY is a popular Indian restaurant." and PlaceID=3's PlaceDescription is "ZZZZZ is a fast food restaurant at Tokyo." and PlaceID=4's PlaceDescription is "AAAAA is a successful restaurant located in Tokyo. It was a restaurant first built in year 2000."

What I want it to return is like

PlaceID Count

1 2

2 1

3 2

4 2

I tried visiting topics at links such as MySql: Count amount of times the words occur in a column but they don't answer my question.

SELECT PlaceID, COUNT(*) FROM Place WHERE PlaceDescription LIKE '%tokyo%' or PlaceDescription LIKE '%restaurant%';

Peter Kim
  • 23
  • 5
  • For your fourth sentence, `Tokyo` appears only once, while `restaurant` appears twice. Why is the total count 2 for searching on `Tokyo restaurant`? – Tim Biegeleisen Sep 13 '19 at 01:49
  • Well the 'restaurant' keyword is already counted so any subsequent 'restaurant' word is disregarded. I'm saying the maximum value of COUNT is 2 and its corresponding minimum value is 0 in this case. – Peter Kim Sep 13 '19 at 01:53

1 Answers1

0

You could try summing your current LIKE expressions. I recommend using REGEXP instead though:

SELECT
    PlaceID,
    (PlaceDescription REGEXP '[[:<:]]tokyo[[:>:]]') +
    (PlaceDescription REGEXP '[[:<:]]restaurant[[:>:]]') AS Count
FROM Place
WHERE
    PlaceDescription REGEXP '[[:<:]]tokyo|restaurant[[:>:]]';

Note that I am doing a regex search for \btokyo\b or \brestaurant\b, meaning that REGEXP would only find those exact words, surrounded by word boundaries on both sides. Also note that REGEXP is not case sensitive, so both tokyo and Tokyo would be picked up and counted.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360