MySQL count matching words

Question

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

Answer 1

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 =)

Answer 2

I suggest that you do that outside SQL, no matter the engine, Regular Expressions are more suited for that than SQL Language. You could probably do that with a view or something but as I said, there are more proper ways to do it like the string manipulation class/object/function from your language or regular expressions.

Answer 3

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

Answer 4

If you only need to search for one term at a time, a function which does something like

select (len(field)-len(replace,find,''))/len(find)

would work in sql server, but I'm not sure about MySQL. You'd also need to take care of the special case len(find) = 0.