Artful Common Queries

Count delimited substrings

from the Artful Common Queries page

Here is a function to count substrings delimited by a constant delimiting string:

DROP FUNCTION IF EXISTS strcount;
SET GLOBAL log_bin_trust_function_creators=1;
DELIMITER |
CREATE FUNCTION strCount( pDelim VARCHAR(32), pStr TEXT) RETURNS int(11)
BEGIN
  DECLARE n INT DEFAULT 0;
  DECLARE pos INT DEFAULT 1;
  DECLARE strRemain TEXT;
  SET strRemain = pStr;
  SET pos = LOCATE( pDelim, strRemain );
  WHILE pos != 0 DO
    SET n = n + 1;
    SET pos = LOCATE( pDelim, strRemain );
    SET strRemain = SUBSTRING( strRemain, pos+1 );
  END WHILE;
RETURN n;
END |
DELIMITER ;
-- example call:
SET @str  = "The quick brown fox jumped over the lazy dog"; 
SET @delim = " "; 
SELECT strCount(@delim,@str);

Last updated 22 May 2024