mysql / count occurrences
Voilà, a MySQL function to count occurrences of a character (or a string of characters).
DROP FUNCTION IF EXISTS OCCURRENCES;
delimiter //
CREATE FUNCTION OCCURRENCES (`needle` VARCHAR(255), `hackstack` TEXT)
RETURNS INT
NOT DETERMINISTIC READS SQL DATA
SQL SECURITY INVOKER
BEGIN
DECLARE `result` INT DEFAULT -1;
DECLARE `pos` INT DEFAULT 0;
DECLARE `skip` INT DEFAULT LENGTH(`needle`);
REPEAT
SET `pos` = (SELECT LOCATE(`needle`, `hackstack`, `pos` + `skip`));
SET `result` = `result` + 1;
UNTIL `pos` = 0 END REPEAT;
RETURN `result`;
END;
//
delimiter ;
Now you can do things like this:
mysql> select occurrences('axa', 'axaxaxa') as how_many_axas;
+---------------+
| how_many_axas |
+---------------+
| 2 |
+---------------+
1 row in set (0.00 sec)
Or, it could be put to useful use in cases like sorting domain names by top level domain first.
See this patch to PowerAdmin 2.1.5 (view) for an example. It fixes a couple of sorting problems, and adds login logging to syslog. The login logging — in turn — can be used to block attempts using fail2ban (view).