Two functions that work just like FIND_IN_SET, but support the % wildcard.
SELECT FIND_WILD_IN_SET(‘%tack’, ‘haystack,bale,heap,needle,pile’);
SELECT FIND_IN_WILD_SET(‘pin’, ‘haystack,bale,heap,needle,pi%’);
DROP FUNCTION IF EXISTS FIND_IN_WILD_SET;
DROP FUNCTION IF EXISTS FIND_WILD_IN_SET;
DELIMITER //
CREATE FUNCTION FIND_IN_WILD_SET(theString text, theSet text)
RETURNS boolean
DETERMINISTIC
BEGIN
DECLARE delimiterCount int;
DECLARE pos int DEFAULT 0;
DECLARE setElement text;
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);
SET returnValue := returnValue OR (theString LIKE setElement);
SET pos := pos + 1;
END;
END WHILE;
RETURN returnValue;
END//
CREATE FUNCTION FIND_WILD_IN_SET(theString text, theSet text)
RETURNS boolean
DETERMINISTIC
BEGIN
DECLARE delimiterCount int;
DECLARE pos int DEFAULT 0;
DECLARE setElement text;
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);
SET returnValue := returnValue OR (setElement LIKE theString);
SET pos := pos + 1;
END;
END WHILE;
RETURN returnValue;
END//
DELIMITER ;