每个字段的regex匹配的MySQL计数数量 [英] Mysql count number of regex match per field

问题描述

我正试图让mysql返回正则表达式匹配的次数.

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

类似:

select 'aabbccaa' regexp 'a'

应返回4(a的4个匹配项)，而不是仅返回true(或1).

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

围绕它的任何方式?

谢谢！

推荐答案

您可以创建一个函数:

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 ;

您可以这样打电话然后

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

这篇关于每个字段的regex匹配的MySQL计数数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！