Comment trouver le nombre d’occurrences d’une chaîne de caractères dans MySQL?  J’en avais déjà parlé ici en français et aussi en anglais.

Pour se faciliter la vie, rien de mieux qu’une fonction pour faire le travail!

SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='STRICT_TRANS_TABLES';

DROP FUNCTION IF EXISTS occurrencesOf;

DELIMITER //
CREATE FUNCTION occurrencesOf ( sourceString VARBINARY(255), searchString VARBINARY(255))
RETURNS MEDIUMINT UNSIGNED
DETERMINISTIC
LANGUAGE SQL
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT 'Returns the number of occurrences of <searchString> inside <sourceString>'

BEGIN
DECLARE occ MEDIUMINT UNSIGNED; …

This was originally posted in French here.

There’s no string function in MySQL (and many other databases!) to help you find the number of occurrences of a string within another string.  For example, how many times does « abc »  appear in « abcbcbabcbacbcabcababcabacb » ?

I was asked this question on IRC a long time ago. Some poor soul was trying to find a particular subsequence in a genomic string (for instance « TAT ») in the following sequence :

ATTGGTGGGCTCTACTAAGATATCAACGGGACTTCGGAGCGTGCCGCACTATTT

Obviously, you can use your favorite programming language and do this kind of search programmatically but is there a way to do it in SQL?

Luckily, the answer is yes!  The solution is simple and looks like …