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