MySQL query finding values in a comma separated string

Question

I have a field COLORS  varchar 50   in a my table SHIRTS that contains a comma delimited string such as 1 2 5 12 15   Each number representing the available colors  When running the query select   from shirts where colors like   1   to get all the red shirts  color 1   I also get the shirts whose color is grey   12  and orange   15   How should I rewrite the query so that is selects ONLY the color 1 and not all colors containing the number 1

User · Answer

Take a look at the FIND IN SET function for MySQL   SELECT        FROM shirts      WHERE FIND IN SET  1  colors   gt  0

User · Answer

If you re using MySQL  there is a method REGEXP that you can use     http   dev mysql com doc refman 5 1 en regexp html operator regexp  So then you would use   SELECT   FROM  shirts  WHERE  colors  REGEXP   b1 b

User · Answer

You can achieve this by following function   Run following query to create function   DELIMITER    CREATE FUNCTION  TOTAL OCCURANCE   commastring  TEXT   findme      VARCHAR 255   RETURNS int 11  NO SQL -- SANI  First param is for comma separated string and 2nd for string to find  return ROUND                    LENGTH commastring          - LENGTH  REPLACE   commastring  findme                 LENGTH findme               And call this function like this  msyql gt  select TOTAL OCCURANCE  A B C A D X B AB    A

User · Answer

1  For MySQL   SELECT FIND IN SET 5  columnname  AS result  FROM table   2 For Postgres SQL    SELECT    FROM TABLENAME f WHERE  searchvalue    ANY  string to array COLUMNNAME          Example  select    from customer f where  11    ANY  string to array customerids

User · Answer

This will work for sure  and I actually tried it out   lwdba localhost  DB test     DROP TABLE IF EXISTS shirts  Query OK  0 rows affected  0 08 sec   lwdba localhost  DB test     CREATE TABLE shirts     - gt    lt BR gt      - gt  id INT NOT NULL AUTO INCREMENT PRIMARY KEY      - gt  ticketnumber INT      - gt  colors VARCHAR 30      - gt     lt BR gt  Query OK  0 rows affected  0 19 sec   lwdba localhost  DB test     INSERT INTO shirts  ticketnumber colors  VALUES     - gt   32423  1 2 5 12 15        - gt   32424  1 5 12 15 30        - gt   32425  2 5 11 15 28        - gt   32426  1 2 7 12 15        - gt   32427  2 4 8 12 15    Query OK  5 rows affected  0 06 sec  Records  5  Duplicates  0  Warnings  0  lwdba localhost  DB test     SELECT   FROM shirts WHERE LOCATE CONCAT      1       CONCAT     colors        gt  0   ---- -------------- --------------    id   ticketnumber   colors          ---- -------------- --------------     1          32423   1 2 5 12 15       2          32424   1 5 12 15 30      4          32426   1 2 7 12 15     ---- -------------- --------------  3 rows in set  0 00 sec    Give it a Try

User · Answer

You should actually fix your database schema so that you have three tables   shirt  shirt id  shirt name color  color id  color name shirtcolor  shirt id  color id   Then if you want to find all of the shirts that are red  you d do a query like   SELECT   FROM shirt  color WHERE color color name    red    AND shirt shirt id   shirtcolor shirt id   AND color color id   shirtcolor color id

User · Answer

If the set of colors is more or less fixed  the most efficient and also most readable way would be to use string constants in your app and then use MySQL s SET type with FIND IN SET  red  colors  in your queries  When using the SET type with FIND IN SET  MySQL uses one integer to store all values and uses binary  and  operation to check for presence of values which is way more efficient than scanning a comma-separated string   In SET  red   blue   green     red  would be stored internally as 1   blue  would be stored internally as 2 and  green  would be stored internally as 4  The value  red blue  would be stored as 3  1 2  and  red green  as 5  1 4

User · Answer

All the answers are not really correct  try this   select   from shirts where 1 IN  colors

User · Answer

The classic way would be to add commas to the left and right   select   from shirts where CONCAT      colors       like    1      But find in set also works   select   from shirts where find in set  1  colors   lt  gt  0

User · Answer

FIND IN SET is your friend in this case  select   from shirts where FIND IN SET 1 colors

User · Answer

select   from shirts where find in set  1  colors   lt  gt  0   Works for me

[sql] MySQL query finding values in a comma separated string

Examples related to sql

Examples related to mysql

Examples related to database