Author: admin

[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:

The better design of the attributes would be to have a separate table with customer id and attribute as follows:

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)