Mysql count number of regex match per field

Question

I'm trying to get mysql return the number of times a regex matches.

something like:

select 'aabbccaa' regexp 'a'

should return 4 (4 matches of a), rather than just true (or 1).

any way around it???

thanks !!

Answer 1

I think that there is no regex engine that will do this. Regular expressions can't count. Of course, most regex dialects have some sort of findall() method, and you can then count the number of matches yourself.

MySQL, however, doesn't have this functionality. The LOCATE function only takes strings, not regexes - otherwise you could have worked with that.

Answer 2

You could create a function:

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 ;

Which you can call then like this

SELECT substrCount('aabbccaa', 'a') `count`;