mysql query to select rows from a subset of a column values

Question

I have a table that contain following values separated by comma in column 'abc'.

(p,q,r,s,t)

I want to create a mysql select query that can fetch rows that matches any value from this subset. e:g if i want to fetch rows that with matching character 'p' then it should display that row which contains 'p' among one of the values.Any suggestions? I tried googling a lot but it didn't help me

Not sure whether the php tag should be there since I can't see a php part to the question, but added a mysql tag. — Joachim Isaksson, Jul 26 '14 at 08:21
Note that the database is designed in a way that won't allow indexes to be easily used. Your queries on comma separated values will be _much_ slower than if you create a row per value. — Joachim Isaksson, Jul 26 '14 at 08:24
I think you want this: http://stackoverflow.com/q/1096679/3696548 — GouxLord, Jul 26 '14 at 08:25

score 2 · Accepted Answer · answered Jul 26 '14 at 08:21

2

You can use LIKE - e.g. select * from my_table where abc like '%q%' or abc like '%r%'

answered Jul 26 '14 at 08:21

Ofir

1,565
3
23
41

@user3848480 One thing to beware of using LIKE in this way, if you're looking for `%a%`, both `a,b,c` and `Mary,John,Kate` will match. – Joachim Isaksson Jul 26 '14 at 08:52
1

I second that but how would John match the %a%? :) – Ofir Jul 26 '14 at 08:59

mysql query to select rows from a subset of a column values

1 Answers1