MySQL IN statement: does ID exist in a comma separated string

Databases
1

Hi,
I have a property database and one of the fields contains a comma separated list/string which are property types; this field data looks like: 1,2,3,4,5,6

What I need to do is to query the database and ask whether an ID (a variable) (property type) exists in that string. In plain english I’d say: is “2” in “1,2,3,4,5,6”?

I’ve tried using “IN” but it doesn’t work as it’s the database field that contains the comma separated string and the ID is the external variable/query - i.e. I’ve seem examples of an IN statement like this:

(in all of these examples I’ll use “2” as an example of a query variable)

SELECT * FROM property_tbl
-> WHERE property_type IN (1,2,3,4,5,6);

but in my case I need it to be the other way around:

SELECT * FROM property_tbl
-> WHERE 2 IN (property_type);

(where a value of property_type might be “1,2,3,4,5,6”)

At the moment I’m using an ugly method to get this working that looks like this:

SELECT * FROM property_tbl
-> WHERE (property_type LIKE (‘2’) OR property_type LIKE (‘2%’) OR property_type LIKE (‘%2’) OR property_type LIKE (‘%2%’))

this works but I figured there must be a better way of doing it.

Can anyone help.
Many thanks.

2

the only better way of doing it is to normalize the table

any time you store multiple values inside a single column, you are asking for exactly this type of trouble (it violates first normal form, in case you want to do some research)

it’s okay to store it that way, if you only ever retrieve it by table key, but if you try to search inside that column, you get this nasty ****-up

for one thing, it doesn’t scale, because it requires a table scan, so the more rows in the tables, the slower the search

here’s a more compact form of the WHERE condition…

WHERE CONCAT(',' , property_type , ',') LIKE '%,2,%'

helps?

3

Hi r937,

Thanks very much for the speedy reply and the for the helpful advice. Looks like normalizing the data is the way to go.

4

Normalizing is almost always the way to go.