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 ;
I found this very useful. We have a column with a long text string, a product description, and I was asked to find all that contained any words from a long list provided.
This worked perfectly. Thanks!
Comment by dgig — June 25, 2014 @ 10:52 am