compare 2 strings seprate by comma in sql query

Question

I have a table with fields like this

category
--------------
20,14,13,16,19

And i have this string:

20,16,9,5

I want to find products that have this categories

in mysql query.

someone have an idea?

Answer 1

We can match the comma separated values ,

Here is a Mysql Query

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 (tens.d*10 + ones.d) IN (20,14,13,16,19)
AND (tens.d*10 + ones.d) IN ( 20,16,9,5 )
ORDER BY n

Answer 2

$myString = '20,16,9,5';

$sets = array();
foreach(explode(',', $myString) as $individualValue) {
    $sets[] = 'FIND_IN_SET(' . $individualValue. ', category) > 0';
}
$whereClause = implode(' OR ', $sets);

$myQuery = 'SELECT * FROM mytable';
if ($whereClause > '') {
    $myQuery .= ' WHERE ' . $whereClause;
}

Answer 3

That depend how your table is but if your table is like :
id_product | id_category | name | other fields
Try that kind of query: SELECT

$category_array= '20,16,9,5';
$query = "SELECT * FROM MYTABLE WHERE id_category IN (" . $category_array . ")";

Answer 4

Just to complete the set of answers here is another one that should come with a health warning.

This assumes the exact same interpretation of the question as: QuentinJadeau.

That is that the input consist of a string which contains a comma separated list of 'categories'.

I have tested it. I tried to set up an 'SQLFiddle' but came second. ;-/ I can post the results.

Sadly, it works as expected, which is similar to the answer 'MarkBaker' provided. The only difference is that this is a function in the database rather than the way he did it. This is the second best way as you can do a lot more when you can generate the complete SQL query dynamically. ;-/

I did this answer 'cos i wanted to write some 'mysql' functions that did something 'useful'.

FIND_IN_COMMA_LIST function

it is a 'mysql' function that takes a comma separated source list, splits it and compare each item with the target comma separated list, using the mysql 'find_in_set' function. It returns the first match it finds.

tested: Server version: Mysql 5.5.16

Example below will return 'x'

SELECT find_in_comma_list('1,2,x', 'a,b,c,9,x')

DROP FUNCTION IF EXISTS FIND_IN_COMMA_LIST;
DELIMITER $$
/*
  needles  => comma separated list of items
  haystack => comma separated list of items

  returns first item in 'needles' list that is found in the 'haystack' list
*/
CREATE
    FUNCTION `testmysql`.`FIND_IN_COMMA_LIST` (needles VARCHAR(128), haystack VARCHAR(255))
    RETURNS VARCHAR(255)
    BEGIN
      DECLARE which INT DEFAULT 0;
      DECLARE result VARCHAR(255) DEFAULT '';
      simple_loop: LOOP
         SET which = which + 1;
         SET result = SPLIT_STR(needles, ',', which);
         IF result = '' THEN
            LEAVE simple_loop;
         END IF;
         IF FIND_IN_SET(result, haystack) != 0 THEN
            LEAVE simple_loop;
         END IF;         
       END LOOP simple_loop;
     RETURN result; 
    END$$

DELIMITER ;

It relies on an other function i found elsewhere.

DROP FUNCTION IF EXISTS SPLIT_STR;
DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    FUNCTION `testmysql`.`SPLIT_STR`(haystack VARCHAR(255), delim VARCHAR(16), which INTEGER)
    RETURNS VARCHAR(255)
    BEGIN
      RETURN REPLACE(
                     SUBSTRING(SUBSTRING_INDEX(haystack, delim, which),
                               LENGTH(SUBSTRING_INDEX(haystack, delim, which - 1)) + 1),
       delim, '');
    END$$

DELIMITER ;