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.
You could create a function to be used directly within SQL, in order to do it all in one step.
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?
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 =)