0

I have created two arrays with GROUP_CONCAT function. Now, I need to check if the first one is contained in the second one.

Here's some 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 covered.

lemon
  • 14,875
  • 6
  • 18
  • 38
betta7391
  • 103
  • 9
  • 3
    Why have you used `GROUP_CONCAT()`? This is much easier to do with the original normalized data. MySQL has no easy way to do this with comma-separated values. – Barmar Jun 06 '22 at 15:05
  • 1
    The basic function for searching for a value in a comma-separated string is `FIND_IN_SET()`. But you need to call it separately for each value to search for, there's nothing that will loop over all the items. – Barmar Jun 06 '22 at 15:07
  • @Barmar I need to group the t1 rows and i need to maintains the information of a field for each row. Like for a team: you group by for team's name and want to maintains all players name for each country. After, you group the players for the nations teams and try to search if the players of a team, for a nation, are players of the nation team too. I also tried `FIND_IN_SET()` but, how you said is not helpful for my case – betta7391 Jun 06 '22 at 15:46
  • 1
    @Barmar maybe a regex can help me? You know? – betta7391 Jun 06 '22 at 15:50

1 Answers1

0

You can try with the following regex syntax:

SELECT @arr2 REGEXP CONCAT('(^|,)', REPLACE(@list1, ',', ',([0-9]+,)*'), '(,|$)'),
       @arr2 REGEXP CONCAT('(^|,)', REPLACE(@list2, ',', ',([0-9]+,)*'), '(,|$)'),
       @arr2 REGEXP CONCAT('(^|,)', REPLACE(@list3, ',', ',([0-9]+,)*'), '(,|$)'),
       @arr2 REGEXP CONCAT('(^|,)', REPLACE(@list4, ',', ',([0-9]+,)*'), '(,|$)'),
       @arr2 REGEXP CONCAT('(^|,)', REPLACE(@list5, ',', ',([0-9]+,)*'), '(,|$)'),
       @arr2 REGEXP CONCAT('(^|,)', REPLACE(@list6, ',', ',([0-9]+,)*'), '(,|$)'),
       @arr2 REGEXP CONCAT('(^|,)', REPLACE(@list7, ',', ',([0-9]+,)*'), '(,|$)')

Basically replaces the commas with a regex that matches at least a comma, and any combination of numbers followed by a comma, then encloses this between:

  • the start of string or comma (^|,)
  • the end of string or comma (,|$)

In this way 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 CONCAT(...) = 1, 'YES', 'NO')

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

Check the demo here.

lemon
  • 14,875
  • 6
  • 18
  • 38
  • It's a good solution but, there is a drawback: for your regex `'[,0-9]+'` if the **@arr2** is `'1,14,2,3'` and the **@list** is `1,4`, there is a match and it's not correct for what I need. I need to match only the whole numbers. I'm not familiar with regex, so if you can help me wuold be great! :) – betta7391 Jun 07 '22 at 08:32
  • You're definitely right, I've just updated my answer with an improved version of it. @betta7391 – lemon Jun 07 '22 at 09:20
  • better, but still with drawback: `@arr2 = '1,1444,2,3'` matchs `@list4 = '1,144'`. I'm trying to use this one `^(?=.*\b144\b)(?=.*\b1\b).*$` and it's seem to work fine in the little example that we are using, but in my real query no.. I don't now why – betta7391 Jun 07 '22 at 10:20
  • 1
    https://www.db-fiddle.com/f/9UJAAGygLzZgCpkgSvVUbg/5 can you tell me if this works in your query? @betta7391 – lemon Jun 07 '22 at 10:27
  • 1
    It works!! Thank you so much! :) but can you explain me why mine doesn't work? If you know – betta7391 Jun 07 '22 at 10:43
  • can you share the fiddle corresponding to your regex up here (`^(?=.*\b144\b)(?=.*\b1\b).*$`) that works with the examples? @betta7391 – lemon Jun 07 '22 at 10:52
  • https://www.db-fiddle.com/f/aykTccB4dpMiej1n2eW3yk/0 this is the link – betta7391 Jun 07 '22 at 12:56
  • I've been looking for a while at your regex, it's really difficult to understand what outlying cases are not allowed by your solution, though I may give you a small general tip to follow: as long as `.` matches any character (except new line), it can easily be a source of error. My recommendation is to avoid using it unless you can't have any other option to follow up with. If you want to debug further the issues of your query, I'd suggest to reverse your regex condition (with a `NOT`) and check for the matched values that actually should be included in the solution of this problem. @betta7391 – lemon Jun 07 '22 at 13:23
  • Wouldn't `FIND_IN_SET()` be simpler? – Rick James Jun 20 '22 at 15:16
  • Didn't know the existence of this method, that's a great tip, thanks for sharing! @RickJames – lemon Jun 20 '22 at 15:18