MySQL - Function name: FIND_IN_SET

Function name: FIND_IN_SET
Aliases: -
Function type: String function
Purpose: Return position of value in string of comma-separated values

Description: FIND_IN_SET() looks for the first occurrence of a string within another string containing comma-separated values:

FIND_IN_SET('r','c,a,r,s,t,e,n, ,p,e,d,e,r,s,e,n') => 3

Usage example: Suppose I have a table of vehicle types and colors, representing the available color range for vehicles in my shop. No type/color combination can appear more than once, so the primary key spans (type, color). It holds the following data:

mysql> select * from vehicle_colors;
+------+-------+
| type | color |
+------+-------+
| bike | blue |
| bike | green |
| suv | blue |
| van | green |
+------+-------+

If I want to know

* the vehicles that are available in either blue or green
* whether they are available in blue or green or both

I can get this information with a single query like so:

SELECT type, SUM(FIND_IN_SET(color, 'blue,green')) AS which_colors
FROM vehicle_colors
WHERE color IN ('blue', 'green')

GROUP BY type;
+------+--------------+
| type | which_colors |
+------+--------------+
| bike | 3 |
| suv | 1 |
| van | 2 |
+------+--------------+

The output tells me that bikes, suvs and vans may all be found in either blue or green (the fact that they are listed at all) and that bikes are available in both colors (1+2=3), suvs in blue only (1) and vans in green only (2).