A Little Noise

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 ;