MySQL count matching words

How to query to get count of matching words in a field, specifically in MySQL. simply i need to get how many times a "search terms"appear in the field value.

for example, the value is "one two one onetwo" so when i search for word "one" it should give me 3

is it possible? because currently i just extract the value out of database and do the counting with server side language.

Thank you

Answers


You could create a function to be used directly within SQL, in order to do it all in one step.

Here is a function which I found on the MySQL website :

delimiter ||
DROP FUNCTION IF EXISTS substrCount||
CREATE FUNCTION substrCount(s VARCHAR(255), ss VARCHAR(255)) RETURNS TINYINT(3) UNSIGNED LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA
BEGIN
DECLARE count TINYINT(3) UNSIGNED;
DECLARE offset TINYINT(3) UNSIGNED;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s = NULL;

SET count = 0;
SET offset = 1;

REPEAT
IF NOT ISNULL(s) AND offset > 0 THEN
SET offset = LOCATE(ss, s, offset);
IF offset > 0 THEN
SET count = count + 1;
SET offset = offset + 1;
END IF;
END IF;
UNTIL ISNULL(s) OR offset = 0 END REPEAT;

RETURN count;
END;

||

delimiter ;

You should use it like this :

SELECT substrCount('one two one onetwo', 'one') `count`; // Returns 3

Are you looking to find a query that, given a list of words, returns the number of matching words in a database field?

eg:

Database table has

ID    Terms
1     cat, dog, bird, horse

then running a check on the words "cat, horse" returns 2?

If so, I suggest you do your checking outside of SQL, in whatever language you're doing the rest of your processing in. SQL isn't designed for this level of processing.

You could possibly use a stored procedure to cycle through what words you're needing to check, but I doubt it would be efficient or highly effective.

Of course, if I'm misinterpreting your request, I could be all wrong =)


Need Your Help

In Python threading, how I can I track a thread's completion?

python multithreading

I've a python program that spawns a number of threads. These threads last anywhere between 2 seconds to 30 seconds. In the main thread I want to track whenever each thread completes and print a mes...

HTML encoding issues - "Â" character showing up instead of " "

html vb.net encoding utf-8 iso-8859-1

I've got a legacy app just starting to misbehave, for whatever reason I'm not sure. It generates a bunch of HTML that gets turned into PDF reports by ActivePDF.