1

I'm generating a string of values (colors) like so:

  'red|green|blue|yellow|orange|black'

Can I pass this as a parameter (param_cols) to a MySQL stored procedure and check against it like so:

  SELECT tbl.products
  FROM table AS tbl
  WHERE tbl.color IN ( param_cols );

Question: Is this possible using a parameter? Should I use IN, LIKE or REGEX?

EDIT:
THANKS for the hints. However I'm more concerned about whether I can pass my list of colors as a variable into the stored procedure vs having to insert each color individually?

frequent
  • 27,643
  • 59
  • 181
  • 333
  • maybe helpful: http://stackoverflow.com/questions/327274/mysql-prepared-statements-with-a-variable-size-variable-list – Thilo Jun 21 '12 at 10:23

2 Answers2

3

There seems to be a FIND_IN_SET function:

 SELECT tbl.products
 FROM table AS tbl
 WHERE FIND_IN_SET(tbl.color , 'red,green,blue,yellow,orange,black');

I am not sure if this can be used with an index, though.

Thilo
  • 257,207
  • 101
  • 511
  • 656
  • Can I do FIND_IN_SET(tbl.color, param_cols ) or not = can I pass the colors as a variable into the stored procedure? – frequent Jun 21 '12 at 10:28
  • 1
    @frequent : yes you can do... Just create string as red,green,blue,y instead of red|green|blue|. **Can you try and see what happen?** – Fahim Parkar Jun 21 '12 at 10:30
  • @FahimParkar: Thanks. Trying now. I need to set up a standalone storedProc, because there must be something else amiss. – frequent Jun 21 '12 at 10:31
  • Yes. Thanks. If you want to make it an answer, I will check! – frequent Jun 21 '12 at 11:00
1

If your colors have an int index, you're better off using that.

But you're on the right track, you can use IN

SELECT tbl.products FROM table as tbl WHERE tbl.color 
IN ('red','green','blue','yellow','orange','black')

This will only look for exact matches though.

Duniyadnd
  • 4,013
  • 1
  • 22
  • 29
  • But can I pass the string as a varialble or do I need to hardcode the color names in MySQL. So can I pass param_cols=('red, 'green', ...)? – frequent Jun 21 '12 at 10:22
  • If you're using another language such as PHP, you can create a string and use that (e.g. $line = "'red','green'";) But if you're using MySQL exclusively, [this link](http://dev.mysql.com/doc/refman/5.0/en/user-variables.html) would help you out a lot more than a short comment – Duniyadnd Jun 21 '12 at 10:53