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).

function snippet mysql