MySQL command to search CSV (or similar array)

Question

I'm trying to write an SQL query that would search within a CSV (or similar) array in a column. Here's an example:

insert into properties set bedrooms = 1,2,3 (or 1-3) title = nice property price = 500

I'd like to then search where bedrooms = 2+. Is this even possible?

Answer 1

The correct way to handle this in SQL is to add another table for a multi-valued property. It's against the relational model to store multiple discrete values in a single column. Since it's intended to be a no-no, there's little support for it in the SQL language.

The only workaround for finding a given value in a comma-separated list is to use regular expressions, which are in general ugly and slow. You have to deal with edge cases like when a value may or may not be at the start or end of the string, as well as next to a comma.

SELECT * FROM properties WHERE bedrooms RLIKE '[[:<:]]2[[:>:]]';

There are other types of queries that are easy when you have a normalized table, but hard with the comma-separated list. The example you give, of searching for a value that is equal to or greater than the search criteria, is one such case. Also consider:

• How do I delete one element from a comma-separated list?
• How do I ensure the list is in sorted order?
• What is the average number of rooms?
• How do I ensure the values in the list are even valid entries? E.g. what's to prevent me from entering "1,2,banana"?

If you don't want to create a second table, then come up with a way to represent your data with a single value.

---

More accurately, I should say I recommend that you represent your data with a single value per column, and Mike Atlas' solution accomplishes that.

Answer 2

Generally, this isn't how you should be storing data in a relational database.

Perhaps you should have a MinBedroom and MaxBedroom column. Eg:

SELECT * FROM properties WHERE MinBedroom > 1 AND MaxBedroom < 3;