The other day, I was toying around with the "M" part in LAMP. I had a set of data and I needed to find a match. Usually one uses something like

select * from dataset where key like '%findme%'.

However, I was dealing with CSVs (comma separated values) and I needed to find a specific value within the CSV. My data looked like that:

Harry,Tom,Bill,George,Frank,John,Elmo,Susan,Mike,Ella

Let's call this data set "names". Now - MySQL has a clever function to deal with this kind of data:

FIND_IN_SET('Frank',names) would return 5, while i.e. FIND_IN_SET('Robert',names) would return 0. But, unfortunately, one can not use wildcards in FIND_IN_SET.

After a little looking, a found a hint on mySQLForge. A function named FIND_WILD_IN_SET. It should work just like the original FIND_IN_SET with wildcards enabled. However, after studying the MySQL source code I understood, that it would just return boolean true if a match would be found and false if not.

I modified the function so that it now really allows for wildcard searches with the right return value:

CREATE FUNCTION FIND_WILD_IN_SET(theString varchar(65535), theSet varchar(65535))
RETURNS int
DETERMINISTIC
BEGIN

DECLARE delimiterCount int;
DECLARE pos int DEFAULT 0;
DECLARE setElement varchar(65535);
DECLARE returnValue boolean DEFAULT FALSE;

SET delimiterCount := CHARACTER_LENGTH(theSet) - CHARACTER_LENGTH(REPLACE(theSet, ',', ''));

WHILE (pos <= delimiterCount) DO
BEGIN
SET setElement := SUBSTRING_INDEX(SUBSTRING_INDEX(theSet, ',', pos+1), ',', -1);
IF (setElement LIKE theString) THEN RETURN pos+1; END IF;
SET pos := pos + 1;

END;
END WHILE;

RETURN 0;

In other words:

FIND_IN_SET('Frank',names) returns 5 and FIND_WILD_IN_SET("%fran%",names) returns 5 as well. A big thanks to Scott Noyes at MySQL Forge for the original code snipplet.