comma seperated list comparison

[Craig Keightley]

is it possible to compare acomma separated list aginst another

eg comma list 1 => 1,2,3,4,5
comma list 2 => 3,5
can you check that 3 is in both, and 5 is in both, therfore they match???

the comparison is to check that if product a who supplies products 1,2,3,4,5
can be used instead of product b who supplies 3,5 as product a already
supplies them

[Bill Karwin]

Craig Keightley wrote:

is it possible to compare acomma separated list aginst another

eg comma list 1 => 1,2,3,4,5
comma list 2 => 3,5
can you check that 3 is in both, and 5 is in both, therfore they match???

the comparison is to check that if product a who supplies products 1,2,3,4,5
can be used instead of product b who supplies 3,5 as product a already
supplies them

Here's a MySQL solution that works for product values 0 through 99. It
uses a cartesian product to generate a list 0..99 (it's easy to add
another digit to make it 0..999). Then it uses a regular expression to
find the value in your comma-separated lists, using the MySQL regexp
token for word boundaries (to prevent "2" from matching "12" or "20").

The result set of this query is two rows, for the values that are common
to both lists: 3 and 5.

SELECT tens.d*10 + ones.d AS n
FROM (SELECT 0 AS d UNION ALL
SELECT 1 AS d UNION ALL
SELECT 2 AS d UNION ALL
SELECT 3 AS d UNION ALL
SELECT 4 AS d UNION ALL
SELECT 5 AS d UNION ALL
SELECT 6 AS d UNION ALL
SELECT 7 AS d UNION ALL
SELECT 8 AS d UNION ALL
SELECT 9 AS d) AS tens
INNER JOIN
(SELECT 0 AS d UNION ALL
SELECT 1 AS d UNION ALL
SELECT 2 AS d UNION ALL
SELECT 3 AS d UNION ALL
SELECT 4 AS d UNION ALL
SELECT 5 AS d UNION ALL
SELECT 6 AS d UNION ALL
SELECT 7 AS d UNION ALL
SELECT 8 AS d UNION ALL
SELECT 9 AS d) AS ones
WHERE "1,2,3,4,5" REGEXP CONCAT('[[:<:]]', (tens.d*10 + ones.d), '[[:>:]]')
AND "3,5" REGEXP CONCAT('[[:<:]]', (tens.d*10 + ones.d), '[[:>:]]')
ORDER BY n

(Unfortunately, we can't use the column alias 'n' in the WHERE clause.
This is a documented limitation of MySQL and the SQL language in general.)

You can replace the regexp comparison with an IN predicate if you can
interpolate your comma-separated lists as string into the SQL query:

....
WHERE (tens.d*10 + ones.d) IN ( $comma_list_1 )
AND (tens.d*10 + ones.d) IN ( $comma_list_2 )
ORDER BY n

Regards,
Bill K.

[Ken Robinson]

Craig Keightley wrote:

is it possible to compare acomma separated list aginst another

eg comma list 1 => 1,2,3,4,5
comma list 2 => 3,5
can you check that 3 is in both, and 5 is in both, therfore they match???
the comparison is to check that if product a who supplies products 1,2,3,4,5 can be used instead of product b who supplies 3,5 as product a already supplies them

Here's a pure PHP solution I just created:
<?
function comp_csv($s1,$s2)
{
$tmp1 = explode(',',$s1);
$tmp2 = explode(',',$s2);
$tmp3 = array_intersect($tmp1,$tmp2);
$smaller_array = (count($tmp1) < count($tmp2))?$tmp1:$tmp2;
$smaller_count = count($smaller_array);
$inboth = FALSE;
if (count($tmp3) == $smaller_count)
if (count(array_diff($tmp3,$smaller_array)) == 0) $inboth =
TRUE;
return($inboth);
}

$csv1 = '1,2,3,4,5';
$csv2 = '3,5';
$x = comp_csv($csv1,$csv2);
var_dump($x);
?>
The function:
1) creates temporary arrays from the CSV strings
2) gets the intersection between the two arrays, which should have no
more elements than the smaller array. It can have less.
3) if it has the same number of elements as the smaller array, it
checks whether the it's elements are the same as those in the smaller
array. If so, the returned value is set to TRUE

Ken
BTW, followups changed to just comp.lang.php