MySQL: arrays contains another array-CodePudding

I have created 2 arrays with GROUP_CONCAT function.

Now, I need to check if the first one is contained in the second one

EXAMPLES:

Arr1: '1,2'
Arr2: '1,2,3'

RESULT: YES

Arr1: '2,3'
Arr2: '1,2,3'

RESULT: YES

Arr1: '1,3'
Arr2: '1,2,3'

RESULT: YES

I have tried to use the LOCATE function, but the third case is not coverd.

CodePudding user response：

Below answer it works , but you might face performance issues. My suggestion is to fix design if possible.

First you need to transform the arrays into columns as I mentioned in another answer here.

To transform the arrays into columns create a number table, with the max character length of the longest array like the following example:

create table numbers (
nr int );

insert into numbers values (1),(2), (3),(4), (5),(6);

Your data example:

create table test(
col1 varchar(255),
col2 varchar(255) );

insert into  test  values 
('1,2','1,2,3');

I'm skipping the splitting part, as you can check it in the other answer.

If below query returns empty set all the data in col1 are included in col2, if data is returned by below query, the returned data are the missing values from col1.

select t1.col1 
from  ( select  SUBSTRING_INDEX(SUBSTRING_INDEX(test.col1, ',', numbers.nr), ',', -1) as col1
        from numbers 
        inner join test on CHAR_LENGTH(test.col1)-CHAR_LENGTH(REPLACE(test.col1, ',', ''))>=numbers.nr-1
      ) as t1 
LEFT JOIN  
      ( select SUBSTRING_INDEX(SUBSTRING_INDEX(test.col2, ',', numbers.nr), ',', -1) as col2
        from numbers 
        inner join test on CHAR_LENGTH(test.col2)-CHAR_LENGTH(REPLACE(test.col2, ',', ''))>=numbers.nr-1
      ) as t2 on t1.col1=t2.col2
WHERE t1.col1  <> all ( select SUBSTRING_INDEX(SUBSTRING_INDEX(test.col2, ',', numbers.nr), ',', -1) as col2
                        from numbers 
                        inner join test on CHAR_LENGTH(test.col2)-CHAR_LENGTH(REPLACE(test.col2, ',', ''))>=numbers.nr-1
                      );

Below you have two fiddles one with empty set and the other with data.

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=d3a7f292d24fde1a35ea711f022afc7b

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=3c8f71bd8cec6dd67e4191a0333e3397

CodePudding user response：

You can try with the following regex syntax:

SET @arr2 = '1,2,3';

SET @list1 = '1,2',
    @list2 = '2,3',
    @list3 = '1,3',
    @list4 = '1,4',
    @list5 = '1,2,3';

SELECT @arr2 REGEXP REPLACE(@list1, ',', '[,0-9] '),
       @arr2 REGEXP REPLACE(@list2, ',', '[,0-9] '),
       @arr2 REGEXP REPLACE(@list3, ',', '[,0-9] '),
       @arr2 REGEXP REPLACE(@list4, ',', '[,0-9] '),
       @arr2 REGEXP REPLACE(@list5, ',', '[,0-9] ');

Basically replaces the commas with a regex that matches any combination of commas and numbers, meaning that arr2 should contain all numbers of the lists, and any other number.

The output is 0 when the elements of the list are not found in arr2, and 1 when they are. If you want to get 'YES' and 'NO', it's sufficient to include the result of the REGEXP operation inside an IF statement:

IF(@arr2 REGEXP REPLACE(@list<n>, ',', '[,0-9] ') = 1, 'YES', 'NO')

Hence replace variable names with the corresponding columns you have in your table.

Try it here.