23 Oct 2009 mysql alternative to PHP substr_count function

The substr_count function in PHP counts the number of substring occurrences. This post describes how to create a mysql stored function to behave just like PHP’s substr_count function.

This function can be created from your mysql console

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 ;

Usage

Example 1

SELECT substrCount('/this/is/a/path', '/') `count`;

`count` would return 4 in this case. Can be used in such cases where you might want to find the “depth” of a path, or for many other uses.
This function is great to count the content of mysql ENUM and SET field data types.

Example 2

SELECT substrcount(
                `tablename` , ','
        ) as tablename
        FROM `tablename`
        where substrcount(
                `tablename` , ','
        ); 

The content of table named tablename is a comma separated list generated from mysql ENUM datatype

2000/2001,2001/2002,2002/2003,2003/2004,2004/2005,2005/2006,2006/2007,2007/2008,2008/2009,2009/2010

In Example 2 the result from this query would be 9, telling us that there are 9 commas in this tablerow.

Source: Posted by Andrew Hanna on August 24 2006 8:04pm

Tags: howto, MySQL, PHP, stored functions

Posted by Hans-Henry Jakobsen