Search in CSV with MySQL

Question

I know this can be done using FIND_IN_SET. But I need something a little different.

My one fields contain 6 digits separated by comma. And other string has at max 5 values separated by comma.

So I want to get the count of matched value.

Like:

Database value: 1,2,3,4,5,6  
Search Value:   1,2,3,4

This will give 4.

Database value: 1,2,3,4,5,6
Search Value:   1,2,3,4,6

This will give 5.

Database value: 1,2,3,4,5,6
Search Value:   1,4

This will give 2.

I'm using have InnoDB tables.

Answer 1

If you weren't using InnoDB tables I would have suggested using a full text search on some description. But you are using InnoDB!

That said I would go down the route of a user defined function specifically a stored function. Luckily I've already got one from working on a previous post. I've tweaked it so that you can pass in any separator. Since it is a function you can use it in queries just as you would any other MySQL function.

Hope it helps.

DROP FUNCTION IF EXISTS `CompareStrings`;

DELIMITER $$

CREATE FUNCTION `CompareStrings`(str1 VARCHAR(255),str2 VARCHAR(255), delimiter VARCHAR(1)) RETURNS double
READS SQL DATA
BEGIN
DECLARE cur_position INT DEFAULT 1 ; 
DECLARE remainder TEXT;
DECLARE cur_string VARCHAR(255);
DECLARE delimiter_length TINYINT UNSIGNED;
DECLARE total INT;
DECLARE result DOUBLE DEFAULT 0;
DECLARE string2 VARCHAR(255);

SET remainder = str1;
SET string2 = concat(delimiter,trim(str2),delimiter);
SET delimiter_length = CHAR_LENGTH(delimiter);
SET cur_position = 1;

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 = concat(delimiter,LEFT(remainder, cur_position - 1),delimiter);
    END IF;
    IF TRIM(cur_string) != '' THEN
        set result = result + (select instr(string2,cur_string) > 0);
    END IF;
    SET remainder = SUBSTRING(remainder, cur_position + delimiter_length);
END WHILE;

RETURN result;

END$$

DELIMITER ;

You can then run something like:

select CompareStrings('1,2,3,4,5,6','1,2,3,4',',')

and you'll get 4 and:

select CompareStrings('1,2,3,4,5,6','1,2,3,4,6',',')

and you'll get 5

Answer 2

I have created 1 function and 1 is found from the mysql site:

This function given the value at the given position in the string with defined separator.

DELIMITER $$

CREATE FUNCTION `SplitString`(x varchar(255), delim varchar(12), pos int) RETURNS varchar(255) CHARSET latin1
return replace(substring(substring_index(x, delim, pos), length(substring_index(x, delim, pos - 1)) + 1), delim, '')

This function uses the above function and serve my purpose.

DELIMITER $$

CREATE FUNCTION `get_matched`(my_num varchar(100), to_match_num VARCHAR(100)) RETURNS int(11)
BEGIN

DECLARE temp, total INT;
SET total=0;
SET temp = IF(FIND_IN_SET(SplitString(my_num,',',1), to_match_num)>0,1,0);
SET total = total+ temp;

SET temp = IF(FIND_IN_SET(SplitString(my_num,',',2), to_match_num)>0,1,0);
SET total = total+ temp;

SET temp = IF(FIND_IN_SET(SplitString(my_num,',',3), to_match_num)>0,1,0);
SET total = total+ temp;

SET temp = IF(FIND_IN_SET(SplitString(my_num,',',4), to_match_num)>0,1,0);
SET total = total+ temp;

SET temp = IF(FIND_IN_SET(SplitString(my_num,',',5), to_match_num)>0,1,0);
SET total = total+ temp;

SET temp = IF(FIND_IN_SET(SplitString(my_num,',',6), to_match_num)>0,1,0);
SET total = total+temp;

RETURN total;
END