Geek asks and answers

Only kickbacks with occurrences x with similar expression

I have a sql expression:

Select * FROM table WHERE content LIKE '%word%'

This will return all rows where the word "word" is in the contents of the column. But is it possible to add a parameter to this operator so that I return strings where the "word" is found two (or three, or four or five ....) times.

+3
source share
2 answers

Stealing @thebjorn's comment, but making it more general:

$theword = "word"; // apply appropriate escaping, especially with user input
                   // you will also need to manually escape % and _
$count = 4;
$like_condition = str_repeat("%".$theword,$count)."%";

$query = "select * from table where content like '".$like_condition."'";
+3
source

you can try this with a procedural function:

  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;

and you can call him that

 SELECT substrCount('word kkhword word jfdj word', 'word') as `counts`; //-- Returns 4

CLOCK DEMO HERE

0
source

More articles:


All Articles