468,544 Members | 1,743 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,544 developers. It's quick & easy.

comma seperated list comparison

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


Jul 23 '05 #1
2 7199
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.
Jul 23 '05 #2

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

Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Craig Keightley | last post: by
2 posts views Thread by Pmb | last post: by
3 posts views Thread by Gary Smith | last post: by
11 posts views Thread by Shawn Odekirk | last post: by
6 posts views Thread by orajit | last post: by
1 post views Thread by UniDue | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.