Count the number of multiple words occurrences within a text field in mysql

Question

I would like to count the occurrences of each keyword from the business_dictionary table when it is found in record ID = 1 in the Risk table.

business_dictionary table:

ID | Keyword
----------------
1  | manage
2  | service
3  | objectives
4  | success
5  | achieved
6  | management
7  | skills
----------------

Risk table:

ID | Description
--------------------------------------------------------------------------------
1  | The quality of service has to be our first priority because the client is here to receive our service. So we have to manage all the areas supporting this service with efficiency.
--------------------------------------------------------------------------------

Do it in the application,mysql is not made to deal with string manipulations — Mihai, Jul 03 '15 at 11:59

score 0 · Accepted Answer · edited May 23 '17 at 11:51

See results at bottom

DELIMITER $$ 

CREATE FUNCTION `getCount`(myStr VARCHAR(1000), myword VARCHAR(100)) 
RETURNS INT 

BEGIN 
DECLARE cnt INT DEFAULT 0; 
DECLARE result INT DEFAULT 1; 
WHILE (result > 0) 
DO SET result = INSTR(myStr, myword); 
IF(result > 0) THEN SET cnt = cnt + 1; 
SET myStr = SUBSTRING(myStr, result + LENGTH(myword)); 
END IF; 
END WHILE; 
RETURN cnt; 
END$$ 
DELIMITER ;

create table business_dictionary
(
    id int auto_increment primary key,
    keyword varchar(40) not null
);

insert business_dictionary (keyword) values ('manage'),('service'),('objectives'),('success'),('achieved'),('management'),('skills')

create table risk
(
    id int auto_increment primary key,
    description varchar(1000) not null
);

insert risk (description) values ('The quality of service blah blah service blah manage blah service with blah');

-- take advantage of ugly non-explicit join finally

select bd.keyword,getCount(r.description,bd.keyword) as theCount
from business_dictionary bd,risk r
where r.id=1
order by theCount desc

keyword         theCount
service         3
manage          1
skills          0
objectives      0
success         0
achieved        0
management      0

function written above shamelessly poached from:

Count occurrences of a word in a row in MySQL

Count the number of multiple words occurrences within a text field in mysql

1 Answers1