compare two comma separated values in mysql and get the matched count

Question

Compare comma separated values in mysql and get the matched output in countcolumn

Example:

id   values 
 1    1,2,3
 2    3
 3    1,3

By comparing id 1 and 2 the output should be 1

By comparing id 2 and 3 the output should be 1

By comparing id 3 and 1 the output should be 2

Are these values available in a table of format `id : value` instead of a comma-separated list? If so this will be far simpler (see http://stackoverflow.com/questions/10375414/compare-similarities-between-two-result-sets ). If you are storing a comma-separated list of values in a `VARCHAR` though, your database structure may be in need of a little improvement. — Simon at The Access Group, Jul 26 '13 at 09:42
How is this related to PHP? And could you give a better example of what you're asking for? — Ja͢ck, Jul 26 '13 at 09:48
Why should the output be 1 when comparing id's 1 and 2? What is the criteria? — Dan Bracuk, Jul 26 '13 at 09:57
@DanBracuk because only 1 item is in both lists 1 and 2. OP is trying to count the number of items that appear in both lists, that is all. — Simon at The Access Group, Jul 26 '13 at 10:01

score 3 · Accepted Answer · edited May 23 '17 at 12:07

Firstly, the data you listed should really be stored in a format where each row of the database stores a single id and a single value, i.e. the 3-element list would correspond to 3 rows. If that change was made to your structure then the following answer would be relevant Compare similarities between two result sets

As it stands though, here's a nice little MySQL function I put together that you could use for your purposes. This takes in 2 arguments which are comma-separated lists and will return the number of elements in list 1 that appear in list 2.

This will not as it stands prevent duplicate IDs in list 1 being counted twice in list 2 (i.e. '1,1,2' and '1,2' would return a value of 3) but you could figure out how to adjust this fairly easily to do that if you so wished.

To use this, just do

SELECT 
    countMatchingElements( '3' , '1,3' ) AS testCase1
    countMatchingElements( '1,2,3' , '1,3' ) AS testCase2
    countMatchingElements( '3' , '1,2,3' ) AS testCase3;

The stored function logic is as follows

CREATE DEFINER = `yourUserGoesHere`@`%` FUNCTION `countMatchingElements`(inFirstList VARCHAR(1000), inSecondList VARCHAR(1000))
 RETURNS tinyint(3) unsigned
    NO SQL
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
    DECLARE numReturn TINYINT UNSIGNED DEFAULT 0;
    DECLARE idsInFirstList TINYINT UNSIGNED;
    DECLARE currentListItem VARCHAR(255) DEFAULT '';
    DECLARE currentID TINYINT UNSIGNED;

    SET idsInFirstList = ( CHAR_LENGTH( inFirstList ) + 1 ) - CHAR_LENGTH( REPLACE( inFirstList , ',' , '' ) );
    SET currentID = 1;

    -- Loop over inFirstList, and for each element that is in inSecondList increment numReturn
    firstListLoop: REPEAT

        SET currentListItem = SUBSTRING_INDEX( SUBSTRING_INDEX( inFirstList , ',' , currentID ) , ',' , -1 );

        IF FIND_IN_SET( currentListItem , inSecondList ) THEN

            SET numReturn = numReturn + 1;

        END IF;

        SET currentID = currentID + 1;

    UNTIL currentID > idsInFirstList
    END REPEAT firstListLoop;

    RETURN numReturn;
END

score 0 · Answer 2 · answered Jul 26 '13 at 09:48

0

if it's a SET type column, you might be able to use & operator and then BIT_COUNT():

BIT_COUNT(values1 & values2)

answered Jul 26 '13 at 09:48

Marek

7,337
1
22
33

compare two comma separated values in mysql and get the matched count

2 Answers2

Linked