[adsense id=”0514458240″ width=”468″ height=”60″]
I have seen many requests for information on how to filter in SQL where a value exists in a comma separated list of values which is stored in a single field.
My first observation is to look very closely at the design of your data model if this is possible. Let us imagine we have a table which contains the following:
| Customer id | attributes |
|---|---|
| 1 | a,b,c |
| 2 | a |
| 3 | b,c |
| 4 | c |
| 5 | a,b |
| 6 | b |
The better design of the attributes would be to have a separate table with customer id and attribute as follows:
| customer id | attribute |
|---|---|
| 1 | a |
| 1 | b |
| 1 | c |
| 2 | a |
| 3 | b |
| 3 | c |
| 4 | c |
| 5 | a |
| 5 | b |
| 6 | b |
By changing the design to this we can add an index on the attribute so that a search on attribute equal to a value will be efficiently performed in the database. A primary key should be added on customer id and attribute which makes sure we cannot add a second attribute of the same value for the same customer id. Also by creating a primary key on customer_id and attribute allows us to quickly return the attributes belonging to a customer_id.
In any case if the design cannot be changed we would need to write the SQL in the following way
- Search for an exact search on an item i.e. only one attribute in the list
- Search for attribute at the starting position in the list i.e. attribute,
- Search for attribute at the end position in the list i.e. ,attribute
- Search for attribute between 2 commas i.e. ,attribute,
Here is an example from a MySQL database:
mysql> create table customers ( customer_id int, attributes varchar(100)); Query OK, 0 rows affected (0.01 sec) mysql> insert into customers(customer_id, attributes) values (1,'a,b,c'),(2,'a'),(3,'b,c'),(4,'c'),(5,'a,b'),(6,'b'); Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM customers WHERE attributes = 'a' OR attributes LIKE 'a,%' OR attributes LIKE '%,a,%' OR attributes LIKE '%,a'; +-------------+------------+ | customer_id | attributes | +-------------+------------+ | 1 | a,b,c | | 2 | a | | 5 | a,b | +-------------+------------+ 3 rows in set (0.00 sec) mysql> SELECT * FROM customers WHERE attributes = 'b' OR attributes LIKE 'b,%' OR attributes LIKE '%,b,%' OR attributes LIKE '%,b'; +-------------+------------+ | customer_id | attributes | +-------------+------------+ | 1 | a,b,c | | 3 | b,c | | 5 | a,b | | 6 | b | +-------------+------------+ 4 rows in set (0.00 sec)