0

I have a SQL Table which has rows like this

--------------------
 id   promocode
--------------------
 1    test,test2
 2    test,test3
 3    test2,test3

Now I want to select rows which has value 'test' in the 'promocode' column.
When I use 

SELECT id from table WHERE promocode LIKE 'test'

it is returning all the rows because all the rows have the string 'test' in them. But I want only 1 & 2 rows because only those two rows have the exact value I need. Can anyone help me with the query?

raghuveer999
  • 699
  • 1
  • 8
  • 24

1 Answers1

6

You can use the function FIND_IN_SET:

SELECT id from table WHERE FIND_IN_SET('test', promocode) > 0

Demo

Note:

Storing comma separated lists in columns is almost ever asking for trouble. Please consider normalizing your database.

VMai
  • 10,156
  • 9
  • 25
  • 34
  • It worked for me Thankyou so much. What is the problem when I use comma separated values? How can I store more than one value in single field? – raghuveer999 Jul 30 '14 at 12:51
  • 1
    If it worked for you, please consider mark this as answer. To your next question: you shouldn't want to :-) Use another table with at least two columns: `id` and `promocode`. The id column is referencing the other table and for every promocode you insert a new row. – VMai Jul 30 '14 at 12:54
  • @raghuveer999 You could look into http://stackoverflow.com/questions/25031397/merge-two-colums-multiple-rows-ignoring-duplicates-mysql to get the latest problem with such multiple values I helped to solve. – VMai Jul 30 '14 at 13:01
  • @raghueveer999, comma separated values defeats the purpose of a db. Each column has some meaning and is meant to store a value. Stuffing 3 values in 1 column, slapping a comma in between, is a poor design. I'm assuming you're going to say 'each entry could have more than 1 promo code' So you either a) design a table with cols pcode1, pcode2, ... pcoden (there has to be some MAX), define pcode 1 as NOT NULL and the rest as NULL (meaning they may not be utilized). The 2nd option is to go with your current id, promocode and have multiple entries per id: 1|test 1|test2 2|test 2|test3... – user3741598 Jul 30 '14 at 14:09