MySQL Substring Count

I needed to count the parts of a string (varchar) which are separated by a `;` (semi colon)
if I use PHP, i always need to fetch the rows and then explode or any thing else. which was really time consuming

A very useful function for counting parts of string using Mysql.

DELIMITER $$

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 ;

and here is the Usage of this function

#
SELECT FROM_UNIXTIME(TIME,'%Y-%m-%d') AS mDate , SUM(substrCount(dlr_url, ';')+1) AS  `count` FROM `sent_sms`;
#

I hope that will help

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.