CODEWITHSUNDEEP
mysql
Gokul Gopala Krishnan
Gokul Gopala Krishnan

Reputation: 323

SELECT mysql column with comma separated values

Consider I have a table like this.

+-------------+---------------+----------------+---------------+
+     id      +    column 1   +    column 2    +    column 3   +
+-------------+---------------+----------------+---------------+ 
+      1      +     values    +     values     + a, b, c, d, e +  
+-------------+---------------+----------------+---------------+

My query can be like this

SELECT * FROM table WHERE column 1 = 'values' AND column 3 = '$variable'

The $variable can be anything from a to e

Is there a way to use query to select the row the I want?

Upvotes: 5

Views: 6623

Answers (4)

Padmanathan J
Padmanathan J

Reputation: 4620

mysql function find_in_set can search only for one string in a set of strings. the first argument is a string, so there is no way to make it parse your comma separated string into strings (you can't use commas in SET elements at all!). the second argument is a SET, which in turn is represented by a comma separated string hence your wish to find_in_set('a,b,c', 'a,b,c,d') which works fine, but it surely can't find a string 'a,b,c' in any SET by definition - it contains commas.

SELECT * FROM tableName WHERE column 1 = 'values' and FIND_IN_SET('a',column3 )

Upvotes: 2

Ganesh Rengarajan
Ganesh Rengarajan

Reputation: 2006

use FIND_IN_SET() -- a builtin function for mysql to search a string 

SELECT  *
FROM    tableName
WHERE column 1 = 'values' and  FIND_IN_SET('a',column 3 )>0

Upvotes: 5

Nisarg
Nisarg

Reputation: 3252

Try this

SELECT  *
FROM    table
WHERE   FIND_IN_SET('$variable', column 3)

Upvotes: 4

Abdul Ghaffar
Abdul Ghaffar

Reputation: 149

use this query select * from table where column 1 = 'values' AND column 3 like '%$value%'

Upvotes: 1

Related Questions