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

How to use a variable in other class that is defined in AppDelegate

ios swift appdelegate

I have a variable var window: UIWindow? in AppDelegate.swift file inside class AppDelegate that I want to use in other class xyz.swift file inside class xyz as explained in here Get current view

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.