How you can query to obtain count of matching words inside a area, particularly in MySQL. simply i have to get the number of occasions a "search phrases"come in the area value.

for instance, the worthiness is "one two one onetwo" then when i look for word "one" it will produce 3

is it feasible? because presently i simply extract the worthiness from database and perform the counting with server side language.

Thanks

Are you currently searching to locate a query that, given a listing of words, returns the amount of matching words inside a database area?

eg:

Database table has


ID    Terms

1     cat, dog, bird, equine

then managing a check up on what "cat, equine" returns 2?

If that's the case, It is best to do your checking outdoors of SQL, in whatever language you are doing the relaxation of the processing in. SQL is not created for this degree of processing.

You can possibly make use of a saved procedure to cycle through what words you are requiring to check on, however i doubt it might be efficient or impressive.

Obviously, if I am misinterpreting your request, I possibly could be incorrectly =)

You can produce a function for use directly within SQL, to be able to do all of it in a single step.

Here's 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 need to use it such as this :

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

I would recommend that you simply do that outdoors SQL, regardless of engine, Regular Expressions tend to be more suited for your than SQL Language. You can most likely do this having a view or something like that but when i stated, you will find more proper ways to get it done such as the string manipulation class/object/function out of your language or regular expressions.

Should you only have to look for one term at any given time, a function which does something similar to

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

works in sql server, but I am unsure about MySQL. You'd should also take proper care of the special situation len(find) = 0.