I would like to create a simple math_intersect function that returns
true if an element is common to two comma-delimited sets of numbers. I
would like it to work like this:
SELECT math_intersect( '1,2,3,4,5' , '4,5,6,7,8' ) /* returns true */
SELECT math_intersect( '1,2,3,4,5' . '6,7,8,9,0' ) /* returns false */

So far, I have a "split_string" procedure and a semi-implemented
"math_intersect" function:

/* REFERENCE: http://forge.mysql.com/snippets/view.php?id=4 */
CREATE PROCEDURE split_string ( IN input TEXT, IN delimiter VARCHAR(10)
)
BEGIN
DECLARE cur_position INT DEFAULT 1;
DECLARE remainder TEXT;
DECLARE cur_string VARCHAR(1000);
DECLARE delimiter_length TINYINT UNSIGNED;

DROP TEMPORARY TABLE IF EXISTS SplitValues;
CREATE TEMPORARY TABLE SplitValues (
value VARCHAR(1000) NOT NULL PRIMARY KEY
) ENGINE=MEMORY;

SET remainder = input;
SET delimiter_length = CHAR_LENGTH(delimiter);

WHILE CHAR_LENGTH( remainder ) > 0 AND cur_position > 0 DO
SET cur_position = INSTR(remainder, delimiter);
IF cur_position = 0 THEN
SET cur_string = remainder;
ELSE
SET cur_string = LEFT(remainder, cur_position - 1);
END IF;
IF TRIM(cur_string) != '' THEN
INSERT INTO SplitValues VALUES (cur_string);
END IF;
SET remainder = SUBSTRING(remainder, cur_position +
delimiter_length);
END WHILE;
END


CREATE FUNCTION math_intersect() RETURNS INTEGER
BEGIN
DECLARE returnval, done INT DEFAULT FALSE;
DECLARE val1, val2 INT;

CALL split_string ('1,2,3,4,5', ','); /* SYNTAX ERROR HERE
*/
DECLARE vals1 CURSOR FOR SELECT value FROM SplitValues;

CALL split_string ('5,6,7,8,9', ',');
DECLARE vals2 CURSOR FOR SELECT value FROM SplitValues;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := TRUE;

OPEN vals1;
outer_loop: LOOP
FETCH vals1 INTO val1;
IF done THEN
CLOSE vals1;
LEAVE outer_loop;
END IF;

OPEN vals2;
inner_loop: LOOP
FETCH vals2 INTO val2;

IF val1 = val2 THEN
SET returnval = TRUE;
END IF;

IF done THEN
CLOSE vals2;
SET done := FALSE;
LEAVE inner_loop ;
END IF;

END LOOP inner_loop;
END LOOP outer_loop;
RETURN returnval;
END

I am receiving a syntax error at in the math_intersect function above
at the line indicated. Note that the comma-delimited strings are
currently hardcoded. My two questions are:

1.) How do I call the split_string procedure within a function?

2.) Is there an easier way to accomplish this trivial task of iterating
through two sets of data and returning common elements?

I understand that this task is really reserved for imperative
languages; however, I really need to understand this SQL function.

Much thanks,

Brad

I found the problem with my SQL. I was trying to CALL a procedure
within a function, which is illegal in MySQL. My solution was to use my
split_string procedure with MySQL's FIND_IN_SET to come up with the
function below.

/* Returns true if an element is common to two comma-delimited sets of
numbers
* Precondition: set2 can't contain more than 64 elements (FIND_IN_SET
limitation)
*/
CREATE FUNCTION intersect ( set1 TEXT , set2 TEXT ) RETURNS INTEGER
BEGIN
DECLARE cur_position INT DEFAULT 1;
DECLARE remainder TEXT;
DECLARE cur_string VARCHAR(1000);

SET remainder = set1;
WHILE CHAR_LENGTH( remainder ) > 0 AND cur_position > 0 DO
SET cur_position = INSTR(remainder, ',');
IF cur_position = 0 THEN
SET cur_string = remainder;
ELSE
SET cur_string = LEFT(remainder, cur_position - 1);
END IF;
IF TRIM(cur_string) != '' AND FIND_IN_SET( cur_string, set2 )
THEN
RETURN TRUE;
END IF;
SET remainder = SUBSTRING(remainder, cur_position + 1);
END WHILE;
RETURN FALSE;
END

SELECT intersect( '1,2,3,4,5' , '4,5,6,7,8' ) /* returns true */
SELECT intersect( '1,2,3,4,5' , '6,7,8,9,0' ) /* returns false */